0

Lets say you have a SalesOrderHeader and a SalesOrderDetail, but there are 2 different sets of SalesOrderHeaders:
MaterialSalesOrderHeader, MaterialSalesOrderDetail and EquipmentSalesOrderHeader, EquipmentSalesOrderDetail.

Both are SalesOrders and almost all header fields are in common, but Equipment Sales Orders have a few different fields than Material Sales Orders.

Would it be better to create my tables this way:

MaterialSalesOrderHeader ---> MaterialSalesOrderDetail  
EquipmentSalesOrderHeader ----> EquipmentSalesOrderDetail

or this way:

--SalesOrderHeader ----> MaterialSalesOrderDetail, EquipmentSalesOrderDetail  
----MaterialSalesOrderInfo (to take care of the different header fields)  
----EquipmentSalesOrderInfo (to take care of the different header fields)

Can anyone give me any recommendatons on setting something like this up? Part of me wants to switch to using the Info tables but the other part of me wants to keep everything like the top example.

JTunney
  • 914
  • 1
  • 15
  • 46
  • It seems like you are using some terminology in multiple ways, such as "detail". How about providing a brief example of the data so that the situation is clearer. – Solomon Rutzky Jun 09 '15 at 13:51
  • Not sure how to provide an example of data in here, it would be way too much. But think of everything as SalesOrderHeader and SalesOrderDetail which is a common thing that most would understand. But then take that SalesOrderHeader and split it into 2 (MaterialSalesOrderHeader, MaterialSalesOrderDetail EquipmentSalesOrderHeader, EquipmentSalesOrderDetail). Would you greate a general SalesOrderHeader table and then split the 2 different SalesOrder types under that as info tables or would you keep all separate tables (i.e. MaterialSalesOrderHeader, EquipmentSalesOrderHeader). – JTunney Jun 09 '15 at 14:08
  • I updated the post with a simple scenario. – JTunney Jun 09 '15 at 14:19
  • 1
    Answer: it depends. 1) it depends on what might happen in the future, do expect more types of SalesOrders? 2) it depends on what the differences are (an example would help), and how much difference there is and how important the differences are. 3) It depends on how you are going to use this data/tables. Will you typically be looking all of the sales orders or only rarely looking at the two types together? – RBarryYoung Jun 09 '15 at 14:27
  • There will not be any more types in the future. The difference between the fields is enough for me to not try combining it all into one Header table (also taking into account more changes between fields in the future). The client wants a listing of all Sales Orders but be able to filter by type, then click into one to view detail. I would prefer that all Sales Orders have unique ideas whether Material or Equipment. – JTunney Jun 09 '15 at 14:30
  • 1
    I would probably go with your second way. note that you might want to implement an instead of insert trigger on the child header tables to insert the records to the parent table first and then insert them into the child table. [read this](http://stackoverflow.com/a/30539462/3094533) for more details. – Zohar Peled Jun 10 '15 at 10:48
  • I ended up going with the second way but was able to do a lot of combining since fields weren't as different as I thought. I didn't need to use additional Info tables. So I just have my Header tables and Detail tables. – JTunney Jun 10 '15 at 12:41

0 Answers0