How to structure my database 1

[YellowOnline]

Hello everyone,

First of all: after 4 years I found this forum back (latest post was in 2004!). Surprise: I had not forgotten my login/pass combo. So soon I'll be helping people again, with 4 years of professional experience in the meantime

I need some help right now: I have less than 48 hours to become sort of an Access-professional. I got hired by a big multinational for an Excel-project but, as it soon turned out, Excel was not the right tool because I need to build a simulator from a database. So we changed to Access. Alas: I haven't worked with Access since 1998 and I never had to make something with such a complexity degree anyway.

As I said: the project is very complex, so unfortunately I can not provide all information. I shall simplify my structural problem so it can be understood easily.

- There are numbered formulas, they have an ID.
- They consist of ingredients with an ID of exactly the same type
- Other formulas can be part of a formula, using the same ID.

Let me give an example with bread:

ID Name         Content1ID Content1 Content1LVL Content2ID Content2 Content2LVL Content3ID Content3 Content3LVL
1  White Bread  2          Flour    80%         3          Water    15%         4          Yeastmix 5%
4  Yeastmix     3          Water    90%         5          Yeast    10%

I need to make a query that returns all information if the user enters an ID number, but in the case of the White Bread here above, it should also return the Yeastmix values.
Actually, it should also recalculate the % in the final product of all those ingredients. And finally, the ContentXIDs are linked to a database which soms up a lot more information that I should get into the final query. I will have 1 big database like this:

ID Name  PriceBakery1 PriceBakery2
2  Flour $1.00        $1.01
3  Water $0.01        $0.01
5  Yeast $0.10        $0.09

There is no way for me to split the ID numbers because this numbering system is used all over the world.

This is driving me nuts. Any help is appreciated.

Kind regards,

YellowOnline







Peace,

Yellow

 

[fneily]

Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Your first table is not normalized. Basically they're 3 steps to normalizing a table and your table violates the first step in multiple ways. First you have duplicated column headings. Drop the numbers and you have Content, Content, Content, etc.
ContentLvl, ContentLVL, etc. This, as in your example, leads to variable length records. Noticed YeastMix only has two ingrediants while White Bread has threee. That means for Yeastmix, for the 3 columns, it'll be blank. Not acceptable in relational database design. Also, what happens if you decide to add a fourth ingrediant? Add more columns? How about 330 more? See, bad design. Tables must stay rectangular data wise.

Now, one product can have many ingrediants and one ingrediant can be in many products. This is a many-to-many relationship. Relational databases don't like this. So you must create a Junction table between the two tables. At minimum, it'll hold the primary keys of the other two tables. So this is what you should have:

tblProduct
ProductID Primary Key
Description
Price
other product info

tblIngredients
IngrediantID Primary Key
Description
IngredPrice
Other Ingrediant info

tblMixture
MixtureID Primary Key
ProductID
IngrediantIDs
Ingrediant%
other COMMON fields

Actually data:
tblProduct
ProductWB White_Bread
ProductYM YeastMix

tblIngrediants
FlID Flour $1.00
WaID Water $0.01
YeID Yeast $0.08

tblMixture
Mix1 ProductWB FlID 80%
Mix2 ProductWB WaID 15%
Mix3 ProductWB ProductYM 5%
Mix4 ProductYM FlID 90%
Mix5 ProductYM YeID 10%

You'll noticed there are no blank fields in tblMixture and it'll stay perfectly rectangular no matter how many ingrediants are in a product. So future changes can be accommodated.
So if you want the ingrediants of White_Bread, then in a query connect tblProduct to tblMixture on ProductID with criteria ProductWB(or just White_Bread under description). And you can do self-join to get the YeastMix ingrediants.

Also, for reasons stated above, you're second table has duplicate column headings. The table should look like:
tblBakery
BakeID
IngrediantID
BakeryName
Markup/down

You would take the base price of the ingrediant from the tblingrediant table and then calculate the cost for each bakery. You would not store each bakery's price.

 

[YellowOnline]

Thank you fneily. I read the link you send me and learnt a lot about proper db-handling. I'm wholly redoing the project atm.