Plotting advanced database relationships

[Guest]

I have information about my products stored in a spreadsheet, and i am keen
to convert to a database, but i am experiencing great difficulty. I written
a small example of the spreadsheet below:

Product Flavour Price Barcode

Wingettes Honey Soy $6.29 10101
Wingettes Barbeque $6.29 10201
Wingettes Honey Soy $5.99 10102
Wingettes Barbeque $5.99 10202
Wings NONE $6.29 20001
Wings NONE $5.99 20002
Drumsticks Honey Soy $6.29 30101
Drumsticks Honey Soy $5.99 30102


I know i need four tables (Product, Flavour, Price and Barcode), but I
cannot distinguish which relationships to have.

Because some Products have no Flavour, I know I need an Outer Join, but
which table do I give it to?

 

[Brett Collings [429338]]

Jamie, I like doing these

You should have 3 tables (maybe 4) and only slightly different to what
you have there.

Because the Barcode is a unique value which represents a specific
combination of Product and Flavour, what you have called the Barcode
table should probably be the table where all of these elements are
assembled and therefore is probably better called tblStockItem.

I'm not sure about tblPrice. There are a huge number of
considerations in how you structure this. Do you have a set price
structure of just a few prices or are different products treated
differently? If you have a tblPrice record item of $6.29, going to
the table and making a price adjustment to $6.49 will change all your
products, no matter what they are, from $6.29 to $6.49. This may be
what you want and if so we can move on

Let's see if I can outline your table structure

tblProduct
----------
ProductID Autonumber Primary Key
ProdName Text What displays in your spreadsheet
ProdDesc Text Description
....any other fields ...

tblFlavour
---------
FlavourID Autonumber Primary Key
FlavName Text What displays in your spreadsheet
FlavDesc Text Description
.... any other fields

tblPrice
-------
PriceID Autonumber Primary Key
Price Number
PriceDesc Text Description or note if required
... any other fields

Now, from those, we bring together the elements from all these other
tables to give you your stock item in a StockItem table

tblStockItem
------------
StockItemID Number Primary Key
Barcode Number Unique (No Duplicates)
ProductID Number Foreign Key looked up form tblProduct
FlavourID Number Foreign Key looked up form tblFlavour
PriceID Number Foreign Key looked up form tblPrice

Now, you Stock Item table is all numbers ... just how it is supposed
to be, this is the Relational Database in operation. For people who
are used to looking at Excel spreadsheets, they get seriously
frightened at this point - don't.

In Access all of the information is displayed via a Query, not in a
table. Data should never be viewed in a table and Forms and Reports
should all be based on Queries, not Tables.

The "lookups" in tblStockItem should all be done on the Forms, not in
the tables.

How's that for starters then??

 

[Craig Hornish]

Hi,

Not to step on any toes but, I disagree with one statement and 1 table
in this example.

The statment: "Now, you Stock Item table is all numbers ... just how it
is supposed to be, this is the Relational Database in operation."

This is not entrirly correct for any table - The Stock Item table could
have a Date, Logical, or text field - The date could be for when the Stock
item was created, the logical could be that the item is discontinued (that
particular flaver), and text - could be extra comments.

I also disagree entirely with the the tlbPrice. A price is one entity
and doesn't need a descriptions as to why it is 6.29 or 5.99. There are
other things about the product that would determine this. In your example
with these two items
there has to be some other factor for the price difference - It could be
size, it could be vendor, etc. That information would be put into the
tblStockItem to differenciate it. It would not go into a tblPrice to
differenciate it because some other price at 5.99 would have a different
reason to be 5.99.

jmho

Craig Hornish
(e-mail address removed) - so I can delete it when it becomes a spam magnet

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"