Primary Keys and Relationships

[Rhonda]

Here's some information on what I'm trying to do. I own a greenhouse and I
want to keep data on inventory, costs and sales. Be able to keep databases
on the cost of supplies and be able to pull them together to determine the
costs of pots, flats, and baskets. I have set up 4 databases

Expenses (contains items not related to sell cost of plants, ie utilities,
repairs,etc.

Seeds and Plugs (contains product id, product name, product description,
cateory ID, units in stock and unit cost

Supplies (contains category id, product name, product description, supplier
id, units in stock, unit price, product id...there is no information in this
field at present)

Tags (contains tagsid, product name, units in stock, unit price, product id
(no information in this field at present)

I let Access choose the primary key and it set up an automatic number is not
the same in each table for the product name.

How do I set up primary and relationships that will link all the tables
together in order to price and inventory sales?

Thanks

Rhonda

 

[DL]

The Tables need to have a common relationship
ie, Suppliers Tbl
Product ID
etc
Sales Tbl
Product ID
etc
The above two are linked on Product ID
Some examples
http://www.databaseanswers.org/data_models/index.htm

 

[John W. Vinson]

Here's some information on what I'm trying to do. I own a greenhouse and I
want to keep data on inventory, costs and sales. Be able to keep databases
on the cost of supplies and be able to pull them together to determine the
costs of pots, flats, and baskets. I have set up 4 databases

Jargon alert: in Access terms, a "Database" is a .mdb (.accdb, .mde, or other)
*container* for multiple tables, forms, reports and other objects. You would
seem to be talking about creating four *tables* in a database, not four
databases - if not, you should be.

Expenses (contains items not related to sell cost of plants, ie utilities,
repairs,etc.

What's the Primary Key of this table? What identifies an individual expense? I
presume this table has a date field indicating when the expense was incurred
(and perhaps another for when it was paid)?

Seeds and Plugs (contains product id, product name, product description,
cateory ID, units in stock and unit cost

It's questionable whether you should store the units in stock at all. It's
often better to store just an initial quantity (as of the date of a physical
inventory, say) and calculate the current amount in stock by using a totals
query to sum all the in and out transactions since that date.

Supplies (contains category id, product name, product description, supplier
id, units in stock, unit price, product id...there is no information in this
field at present)

Same issue.

Tags (contains tagsid, product name, units in stock, unit price, product id
(no information in this field at present)

How are Seeds and Plugs, Supplies, and Tags related? If they all deal with a
product, then there should be one master table of Products with a field that
indicates whether the product is a seed, a plug, a tag, etc.

I let Access choose the primary key and it set up an automatic number is not
the same in each table for the product name.

Correct. Autonumbers are COMPLETELY ARBITRARY, and an autonumber in one table
will have no connection whatsoever with an autonumber in any other table.

How do I set up primary and relationships that will link all the tables
together in order to price and inventory sales?

You'll know that better than we will - what is the real-life, in the
greenhouse, relationship between the Entities (real persons, things or events)
represented by these tables? Each kind of Entity should have its own table;
you need to define what the entities are. Is a Seed a different kind of entity
from a Tag, or are they just two particular examples of a Product entity?
That's a business rule decision that *you* must make.

In any case, you should try very hard indeed to be sure that you don't have
data stored redundantly. If a given product occurs in more than one table
(say, a Products table and a Sales table), then the product name or
description should be stored once and only once; the other tables should have
a ProductID as a foreign key to the Products table primary key, and should not
contain ANY other information from that table.


John W. Vinson [MVP]