...

View Full Version : normalisation with foreign keys



irish87
07-29-2006, 10:39 PM
Can a table have a more than one foreign key from more than one table stored in another table? Ie. can a repair table contain a foreign key from the mechanic table (for the mechanic that did the job) and another foreign key from the Customer table (to specify who the repair is to be charged to)?? Also would this mean the table is still normalised??

Irish87

irish87
07-29-2006, 11:42 PM
re reading my question it may seem a little vague. The table structure below is tblJob, in I have marked the fields which are foreign keys.
I have simply one question:
1. Is there a limit to the amount of foreign keys you can have in the one table all of which are key fields in their own individual table, and will this affect normailisation???

tblJob

JobID (Primary Key)
MechID (Foreign Key)
Auth By (Foreign Key)
Customer ID (Foreign Key)
Registration ID (Foreign Key)
Lab ID (Foreign Key)
Reason
Part ID (Foreign Key)
Qty
Sub Total
Vat ID (Foreign Key)
Total
Paid
DateOfManufacture
JobFinsihed
Date


Irish87.

guelphdad
07-30-2006, 01:55 AM
no problem having that many foreign keys in your tables. you might want to show the layouts from your tables so we can tell you whether or not your tables are properly normalized, or, if not, offer suggestions to fix them.

irish87
07-30-2006, 08:53 PM
ok here goes...... below is the table architecture you requested, also plz note further questions below the list of tables!!

tblAuthorisation
|AuthID(PrimaryKey)|AuthName|

tblCar
|Registration#(PrimaryKey)|CustomerName(ForeignKey)|Make|Model|Type|DateOfManufacture|MOTexpiry|TAXe xpiry|

tblCarParts
|CarPartID(PrimaryKey)|Part#(ForeignKey)|Registration#(ForeignKey)|

tblCustomer
|CustomerID(PrimaryKey)|Name|Address|Town/City|PostCode|Country|TelNo|

tblHourlyRate
|HourlyRateID(PrimaryKey)|HourlyRate|

tblJob
|JobID(PrimaryKey)|
|MechID(F_Key)|
|AuthID(F_Key)|
|CustomerName(F_Key)|
|Registration#(F_Key)|
|Lab#(F_Key)|
|Reason|
|Part#(F_Key)|
|Qty|
|SubTotal|
|VatID(F_Key)|
|Total|
|Paid|
|DateOfManufacture(F_Key)|
|JobFinished|
|Date|

tblLabour
|Lab#(PrimaryKey)|Description|Duration|

tblMechanic
|MechID(PrimaryKey)|MechName|TelNo|

tblOrders
|Order#(PrimaryKey)|CustomerID(F_Key)|SubTotal|VatID(F_Key)|Total|Date|Received|

tblOrderItems
|OrderItemsID(PrimaryKey)|Order#(F_Key)|Part#(F_Key)|Qty|

tblStock
|Part#(primaryKey)|SupplierID(F_Key)|PartName|RetailUnitPrice|QtyInStock|ReOrderLevel|DateIntroduced |DateDiscontinued|

tblSupplier
|SupplierID(PrimaryKey)|SupplierName|Address|Town/City|PostCode|Country|TelNo|

tblVat
|VatID(PrimaryKey)|VatRate|

1.Could someone please tell me if these tables are normalised and if not could they show me what I have done wrong???

2.The reason I asked in my previous post if the table 'tblJob' was normalised was because of the number of foreign keys, but I was wondering if it would be better to split the Job Table into two and have the JobID as the primary key in tblJob and create a table 'tblJobDetails' which would store all the foreign keys that are in the current table, 'tblJob'??? Would it be better to leave the table tblJob in its current state (the way it is listed above) , or should I change it to the way I have just mentioned, by splitting it into two separate tables???

Irish87.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum