Alright, so I'm working on a site for a client. It is a bar, and the owner wants to be able to hand out 'achievements' that people can get at the bar that will show up on the website, and people can post to their Facebook. The actual process looks like this:
- She logs on to the admin area and hits the 'create achievement' button.
- She types in an ID for the achievement, a name, and a description. Then she enters in how many tickets for the site to generate.
- The code takes the information stores it, and generates a bunch of 9 digit codes that people can enter into the site to redeem.
-
It is a pretty simple system, but my question is this. What is the best way to store all of this in a MySQL database?
Right now I see two ways, both of which feel wrong.
First one:
----------------------------------------------------------
Unique ID :: Achievement ID :: Name :: Description :: Code
----------------------------------------------------------
In this, all the codes are stored in a single array which I then search and remove the code that is redeemed.
Second one:
----------------------------------------------------------
Unique ID :: Achievement ID
----------------------------------------------------------
In this, I store the name and description in the first two rows, then have one row for each ticket thereafter.
Any ideas on which method might be better? Or if there is a better one I'm too stupid to think of?
>>1
Wow. It's impressive how much you don't know about SQL. Tables are sets, not ordered lists. Go read a intro book, use natural keys, and normalize your database.
I wasn't treating it as such. I think you may have misunderstood the way I wrote it. I totally didn't think about just grabbing all the entries with the same Achievement ID. I knew I was overlooking something. Thanks.
Been reading up on some of that - I really appreciate it. Data normalization is a very helpful topic. Honestly, I'm a designer, not a programmer (and my client knows this), but she wanted me to program it, so I'm using what I know.
| I'm a designer not a programmer then why are you on /prog/ . Please do not use mySQL if you cannot program. You will do it wrong I guarantee it. Either work with a programmer on this one or use drupal or a cms that will do this for you automatically.
>>14
I'd like to second the "Do not use SQL if you cannot program." SQL is a programming language. It is a domain specific language, not a general purpose language (such as C), but it is still an unforgiving and sophisticated tool.
>>6
Throwing an "ID" field in every table is a newbie mistake when designing a database. It shows that you're not sure where the natural keys are. Read up on "natural keys". It helps to understand what you will do with those keys, so read up on "joins" too.
CREATE TABLE achievements (
a_id integer PRIMARY KEY,
a_name varchar(200) NOT NULL,
a_desc text NOT NULL
);
CREATE TABLE codes (
c_aid REFERENCES achievements (a_id),
c_code CHAR(9) PRIMARY KEY,
c_redeemed boolean NOT NULL,
INDEX (c_aid)
);
>>18
This is not about programming. Writing formulas in an excel spreadsheet is a more intellectually stimulating exercise than anything you will ever do in your life.
>>20
I appreciate the sentiment, and I'll agree that this looks nicer on paper. In the real world, you are going to be massively fucked performance wise when the codes table has billions of entries and you are using a nine character primary key. This will also cause problems should you desire to change the schema, and is likely to create integrity issues if OP decides to expand on this system at some point in the future.
Name:
Anonymous2010-08-29 11:02
>>1 She Are you expecting her to fuck you if you help her?
>>20
I never learned joins so I always do [m]select ... from ... where x in (select ...);[/code]