Database Design

[OriginalSunny]

Hi, just a quick question regarding the design of my database. I have the following 2 tables:

Stock(stockID, modelnum, amount, description, pic)

Stock_Item(modelnum, productname)

I am using php to communicate with the database. Is it better for me to just put the productname in the stock table?? Or should i leave it like this? I am sure the way it is above is better as it is more normalised, however the only problem is extracting the info from the database using php is harder as two tables have to be used.

Any ideas???

p.s by productname i mean things like desktop, accessories or laptop.

[lpxxfaintxx]

Try using Foreign Keys, as I have learned earlier ;)

[SpectralDesign.Net]

I think it depends on what you're trying to do...

Are you satisfied with what you can get from a flat-database? Will it scale to include functions that might be handy in the future? For instance, might you want to keep track of inventory levels with it down the road so that both you and your customers know if an item is sold-out?

Basically, if the relation between any pair of attributes will always be a one-to-one relationship, flat DB (i.e. a single table) should be adequite. If you have any pair of attributes that could be a one-many or a many-many that's when you really need to flex your relationalal DB muscles...

To get away from your stock thing (because I can't think of a good example offhand) here's a case of a one-many (as well as a many-many) situation: people & phone numbers. Let's look at it as a one-many first:

Johnny Blue has a cellphone, a home phone, and a work phone. To normalize your DB properly you'd want two tables:

Table: people
attribute: person_id (numeric, auto-incrememnt, unique)
attribute: name (you might want several attributes for first, last, middle, salutation, etc...)

Table: phones
attribute: phone_id (numeric, auto-incrememnt, unique)
attribute: person_id (this is where your DB gets 'relational'.. you're relating this record to a specific record in an other table)
attribute: number (unique)
attribute: type (LIST: cell, home, work, etc.)

Okay, so that works as long as one person has one or more phones, but what if a phone "has" more than one person? Such as my wife, my son, and myself all having the same number? Okay, now you're talking many-many, and you need a third table to link the people and phones bi-directionally:

Table: people_phones
attribute: person_id (numeric, auto-incrememnt, unique)
attribute: phone_id (numeric, auto-incrememnt, unique)

And you can remove the person_id from the phones table because now it's in this new table. In practice now, Johnny has 3 phones, but his wife has 1, and it's the same as Johnny's home number... If you say Johhny is person_id=1 and his wife =2, and that his home# is phone_id=1, cell =2, work =3 then the data in the people_phones table looks like this:

row1: person_id=1, phone_id=1;
row2: person_id=1, phone_id=2;
row3: person_id=1, phone_id=3;
row4: person_id=2, phone_id=1;

I hope that makes sense!

[fenway]

If at all possible, consider using many-to-many relationships wherever it might conceivably come in handy. It's usually easy to handle in the early stages of development, and a royal pain in the butt to hack in later on.

[SpectralDesign.Net]

Just to make an amendment to Fenway's suggestion....

Anecdote: My first MySQL DB was a super-rudimentary 3-table DB.. one table was only there to fill a set of pull-down menus (country, province type of thing) and two more one-one tables which could have been a single table, but I wanted to save a little space since I didn't think every user would go and make a profile so I kept the membership table and profile tables independent.

Now, my next project that I'm currently working on, is a large web of many-many tables (by necessity) with tables for: persons, addresses, phones, organizations, email_addresses, and more...

PHEW! What a learning curve!

Simply learning how to properly query the tables to draw up proper reports has been a small journey in itself, and ahead of me I'll need to learn how to perform a data migration mapping from a flat-DB (excel, yuk!) which is a looming unknown to me right now, and as well there will be a ton of little details in creating a useable interface to manage (search, edit, add, delete) records from the DB.

But -- you know what? Once you know these things, you're way ahead of the game. Better to learn from the get-go than to keep building DBs that are less than best-practice standard, in my opinion.

[fenway]

Speaking of complicated queries, MySQL 5 has support for views, which at least IMHO allows for semantic representations of data -- regardless of the underlying DB complexity -- in many if not most cases.