SQL Server Performance

Building relationships

Discussion in 'T-SQL Performance Tuning for Developers' started by boofers20, Jun 10, 2005.

  1. boofers20 New Member

    Hi there, thanks to all for helping me with my previous threads but now I have a little bit more of a challenge. I am trying to build relationships for my tables. Here is what I need:

    TableName FieldName(s)
    ========== ===========
    ord_OrderEntry ord_OrderNum
    ord_LineItem ord_OrderNum, ord_LineNum
    ord_IGUnit ord_OrderNum, ord_LineNum, GF.ID
    tblMtlGas MG.ID (GF.ID = MG.ID)
    ord_Glass ord_OrderNum, ord_LineNum, GC.ID, GD.ID
    tblMtlGlassColor MGC.ID (GC.ID = MGC.ID)
    tblMtlGlassDim MGD.ID (GD.ID = MGD.ID)
    ord_Spacer ord_OrderNum, ord_LineNum, SP.ID
    tblMtlSpacer MS.ID (SP.ID = MS.ID)
    ord_Muntins ord_OrderNum, ord_LineNum, MC.ID, MS.ID, MMC.ID
    tblMtlMunColor MMC.ID (MC.ID = MMC.ID)
    tblMtlMunSize MMS.ID (MS.ID = MMS.ID)
    tblMtlMunConfig MCN.ID (MMC.ID = MCN.ID)

    Here are the rules:
    1. ord_OrderEntry will be the master record table
    2. ord_LineItem, ord_IGUnit, ord_Glass will always have at least 1 detail record, with the LineItem table being the 'master' detail record table
    3. ord_Glass could have up to 3 detail records per line item but a min of 1 record
    4. ord_Spacer & ord_Muntins could have 0 detail records per line item but a max of 1 rec
    5. the tblMtlxxxxxx tables are lookup tables and should not have any records deleted when detail records are wiped

    I have already setup the ord_OrderEntry.ord_OrderNum as my primary key and set up a foreign key relationship between the ord_OrderEntry.ord_OrderNum and ord_LineItem.ord_OrderNum. Where the trouble begins for me is setting up the detail tables and relationships.

    I apologize for the amatuer question but this is all new territory for a developer like me. Thanks in advance for any sugestions or help.

    -Mike

Share This Page