Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    normalisation with foreign keys

    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

  • #2
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #4
    New Coder
    Join Date
    Jul 2006
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •