SQL Server Performance Forum – Threads Archive

Building relationships

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

---

]]>