Table design question

[lukyhare]

Hello all, need some help on some table designs, any opinions would be appreciated.

I have 3 tables now that are related and probably need to split these into smaller separate tables.
Ive attached a file with the tables im trying to fix to better show what im working with and another excel file of what i want a report to come out to look like.
Looking at my main table: 11tblPO, I know i need to separate colors into another table. Then i can tie table: 11tblSofa to the color. Then i figure i will need to split 11tblSofa so that i have a table for the cover type, price and quantity. The problem im having is that any way i build a relationship, i see needing to input color, or type or price multiple times.
Example, if i go color to type to price, i will only need to input one color once but will need to input price for every different color of the same type of product.
If i go type to price to color, then i will need to input the same color multiple times for the same type of products.
Im sure everyones lost now. I think ill have to explain more as we go, can anyone give me some suggestions based on looking at my samples?
Thanks and regards,

Len H

 

[Len Boorman]

What you need to do is actually consider your data.

You should design your tables based upon the data and the relationships follow. Do not have preconceived ideas about relationships.

Consider carefully the data dependancies. (These control normalisation)

Sofa Colour Price

Okay so a Sofa will be available in different colours but will the price be different.

If the answer is yes then Colour determines Price so Colour and Price go together

If the answer is No then Sofa and Price go together.

It is these type of Entereprise Rules that you know that determine database design

Above was a simple example. Things need to be driven deeper.

If Colour determines price then How ?, Does Fabric come into the equation.

Hmmm Maybe we have Colour/Fabric/Price going together and maybe some fabrics are only available in certain colours. Gets complex maybe

Data items may occur twice if they are primary keys because they may also exist as foreign keys. Apart from that if they appear twice then tables are not normalised correctly in my opinion.

Keep smiling and remember

Its all in the name

Len B

 

[Mile-O]

I've split some tables but I've not had a chance to add in the tables required for orders.

I'd recommend you look up normalisation - the design you had was set up for a fall from the start.

 

[lukyhare]

First off, Man you guys are quick.
Mile, thanks for making the splits, i should have mentioned earlier that i only copied part of my database that focused on one of the problems i was having. I realize though from your splits, that i named my fields wrong. I should have named them FabricID, ColourID, etc. I was thinking the actual name, like red as the ID but now i think this does not make sense. Im in the process of changing it.
Now Len B (and i agree it is all in the name),

I have 2 products (Just for this sample): 11tblPOCover and 11tblPOCushion
Products are broken down by Style and Size.
Price is determined by Product, style and size.

In the PO (11tblPO)Factory are just there to help identify where the product comes from and dont determine anything else so they can just come from a simple list.

These are the Tables i plan to have (im not including all of the fields, just the relevant ones)

11tblPO
PONumber (Key)
FabricID
ColorID

11tblPOColor
ID (Key)
ColorID (from List)
PONumber

Product tables:

11tblPOCover
CoverID (Key)
POColorID
CoverSizeID (from list) tblCover: ID (key), CoverStyle (e.g. Round Arm)

11tblPOCoverSize
ID (Key)
CoverSizeID (from list) tblCoverSize: ID (Key), CoverSize (e.g. Love Seat)
Quantity
Price?

11tblPOCushion
CushionID (Key)
POColorID
CushionSizeID (from list) tblCushionSize: ID, CushionSize (e.g. Piped)

11tblPOCushionSize
ID (key)
CushionTypeID (from List) tblCushionType: ID, CushionSize(e.g. 18")
Quantity
Price?

I think this works, the problem is that i have to enter the same price multiple times

e.g. Twill Red Roundarm Sofa 2000pcs $2.95
Twill white Roundarm Sofa 1500pcs $2.95

I was thinking that i could just add prices to the table tblCover, but then realized that i cant because if i change the style the price would be wrong
e.g. Red Bowtie Sofa 1200pcs (Would end up being $2.95) which for the style would be wrong. i would need somethin like:
Twill Red Bowtie Sofa 1200pcs $2.85
Im trying to get my table to work so that eventually when i make a report
in each PO i only have to put in fabric once (Since usually one PO is based on one fabric), each color once, and each price (based on the (Fabric, Product, style, size) once. OK Who else is confused now?
I just wanted to post this first since you guys were so quick to get back to me. I think i need to play with my tables first and post my sample as i think its very confusing the way i just wrote it.
Thanks again for the help. Looking forward to seeing what ideas you guys have.

Len

 

[Len Boorman]

First off, Man you guys are quick.

I have 2 products (Just for this sample): 11tblPOCover and 11tblPOCushion
Products are broken down by Style and Size.
Price is determined by Product, style and size.

Looks like you have determined that
Price is determined by Product, style and size. Therefore these fields belong together as a unique set

This is basically determining functional dependancies. viz Normalisation

These are the Tables i plan to have (im not including all of the fields, just the relevant ones)

Hope you do not have any irrelevant ones


You are the one that knows the Enterprise Rules.

Put what you have done to one side and refresh the mind.

Start at the big picture and then start to break it down one level at a time. Do not get into detail on any one part but bring it all down together.


Look for the data dependancies.

Draw a diagram that shows the relationships

Its a drag but it is the only way of doing it really

Len B

 

[mjmartin]

Yeah, normalisation helps.

I'd also recommend drawing an Entity Relationship diagram, it helps you think in 'many and one' style.

Here's an example of an Entity Relationship Diagram:

A user can be a member of Many Groups

A user can have many 'prodlink' records.
A product can have many 'prodlink' records.

A user can therefore have many products registered to them, even
if they have the same product registered twice to them (this accounts
for quantity)

I've put together a short word document which may help you with terminology and normalisation. It's based on a new project I did for a respected hardware and software manufacturer as well as a much older project I did for a small web design company.

 

[lukyhare]

Have been swamped with work and havent had time to fix up my database.
Just wanted to thank you guys for your replies, i really appreciate your time. Im gonna sit down and look at the info you guys have given and see if i can put together a working database for my POs. mjmmartin, I know your samples are gonna help i have looked briefly at your document and will spend some time on them later this week.

Thanks again.

Len