referential integrity

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my database I have 3 tables that show what stock I have or have ever had.
They are called [Teas], [Ceramics] and [Gifts]. Their primary keys are
[TeaCode], [ProductCode] and [GiftCode], resp. (At the moment each of the
above fields has a different format but if it would make life much easier
they can be redefined.) Now I want to add another table [Daily Sales] where I
need to enter fields [Date] and
Code: 
. This [Code] should accept an entry of
either of the 3 primary key fields mentioned above. Or I can have 3 fields
with names corresponding to the primary key fields. In either way I would
like the database to keep the referential inegrity between the [Daily Sales]
and the other 3 tables. Is there some way of doing this? (I am not really
familiar with Access code, so it would help if I could build the database in
design view.)
 
Magdalena

I think you may be approaching this from the wrong direction.

I am assuming that you have several individual products in each of the 3
categories you mentioned.

I think it would be far preferable to have three (4) tables:

ProductCategory
Products
Sale
SaleDetails

The ProductCategory table should have 2 fields:
CategoryID (autonumber) - primary key
CategoryName (text) - Indexed - yes (no duplicates)

The Products table should have 3 fields
ProductID (autonumber) - primary key
CategoryID (number - long integer)
ProductName

The Sale table should just have 2 fields
SaleID (autonumber) primary key
SaleDate
Comments (optional)

The SaleDetails table should have
SaleDetailID (autonumber) - primary key
SaleID (number - long integer)
ProductID (number- long integer)
Qty (number - byte or integer - depends on largest qty of particulkar
prod/sale)
Price (currency)

Note that with the SaleDetails table - establish a unique index made up of
the two fields SaleID & ProductID - THIS IS ABSOLUTELY ESSENTIAL

If you now create the following relationships

ProductCategory table & Product table - create a one to many relationship
using the CategoryID fields in both tables

Sale table & SaleDetails table - create a one to many relationship using the
SaleID fields in both tables

Product table & SaleDetails table - create a one to many relationship using
the ProductID field in both tables

The Category table will contain just 3 records Teas, Ceramics & Gifts.

The Product table will contain probably many records - depending on how many
different teas, ceramics & gifts you have.

Anyway, hope this helps.

Kind regards

Ross
 
Back
Top