Table Design Problems

[KevW]

Can anyone help me solve the problem that I have with the following tables.

SUPPLIER
* SupplierID
Name
Address
Email

SALE
*SaleID
# RegNo
# StaffID
# CustomerID
Date
Sale Price

STAFF
* StaffID
Name
Address
TelNo

CUSTOMER
* CustomerID
Name
Address
TelNo
Email

STOCK
* RegNO
# SupplierID
# SaleID
Make
Model
Colour
PricePaid
ServiceCost

All looks fine with the table design but when you try to do the relationships all the tables will link correctly except for the Sale to Stock relationship. I keep getting the message about referential integrity so I cannot join them togehter.Unfortunately due to the fact that this is for an assignmet for college these are the tables that I have to use. Does anybody have any ideas on what I can do

 

[drkhelmt]

Well, I'd need to know the what the message about integrity says. I might check the datatypes behind the values. If they are not the same, you would get an error message.

On the grander scheme, I would not use real data as a primary key. It appears that all is well except for the stock table. I'm guessing RegNo is real data and should not be the primary key. That's just my personal thoughts on the issue.

Hope this helps.

 

[KevW]

I have checked the data types and played about with the table design and the relationship now shows as 1:M. The problem that I have now is that the SaleID does not show in the Stock table.

 

[drkhelmt]

saleId probably shouldn't show in the stock table

My thoughts on the design so far as the stock relating to the sale is that the stock should not relate to a sale, but rather the stockID should be in a saleItem table. Then the saleItem is related to the Sale.

stock items can have multiple sales
sales can have multiple stocks

Then you can have multiple sale items for each sale solving what looks like a many to many relationship. The saleItem table looks like:

saleItem(_saleItemID_, saleID, RegNo, salePrice ...)

Hope this helps.

 

[KevW]

Thanks for your advice again, I can get the relationship between the Sale table to the Stock table to be 1:M, the only problem is you cannot enter a new item off stock because a related item is required in stock. Which is not always the case as new stock bought from a suplier will not be sold straight away. Will your suggestion solve this dilema.

 

[drkhelmt]

it should

It should. You can add a record to the table on the 1 side of a 1:M relationship, but it does not work vice versa.

Hope this helps.

 

[KevW]

So I am right in thinking that the Sale table should link to the new SaleItem table, then the Sale item table should limk to the Stock table. IF this is the case which table would the SaleItemID PK be the foreign key and what would then be the link on the stock table.

 

[drkhelmt]

saleItemID would be the primary key

saleItemID would be the primary key for the saleItem table. The field saleItem.saleID woudl be the foreign key to the sale table and saleItem.RegNo would be the foreign key to the stock table.

Hope this clarifies.

 

[KevW]

New table design

Hopefully this is what the tables should look like to remove the problems, do you think that this will now work

SALEITEM
*SaleItemID
SaleID
# RegNo


SALE
* SaleID
# SaleItemID
# StaffID
# CustomerID
Date
Sale Price


STOCK
* RegNO
# SupplierID
Make
Model
Colour
PricePaid
ServiceCost