Jump to content

Database Design


OriginalSunny

Recommended Posts

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.
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.