Solved

Data modelling issue

Posted on 2012-12-22
5
320 Views
Last Modified: 2013-01-16
I'm trying to get the optimal model for the following data. Firstly, there's an Orders table. The thing is, for each order, which is single and self-contained (so there's no need for Items/OrderItems) there are different kinds of accompanying information, which i'd like to be as extensible as possible. At the moment, there's Vehicle Info for vehicle-related orders and Field Stand info for stalls.

I was contemplating an Extra_Info (foreign key into an info table) column in Orders and possibly an Order_Type column, but the notion that the value in Order_Type alone determines to which table Extra_Info is a foreign key rings alarm bells with me that the data are modeled wrongly or sub-optimally. What is my best arrangement?
0
Comment
Question by:CEHJ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 165 total points
ID: 38716525
I definitely wouldn't have a foreign key that could link to multiple tables based on a "Type" field in the same table - that's going to be rough for the next person to follow.

Though I feel like it's not optimal, I'd consider something like this - as much for clarity to the next developer as anything else. I'd add a column to the orders table for each type of additional information you're storing - if you're tracking vehicles, as a "Vehicle_Key" column that links to a Vehicles table, and the same for a "FieldStand_Key" field, and so on. That way, each type of data can be located in its own detail/info table, and with the foreign key set up properly.

Also, you can handle orders where one of those doesn't apply (like an order without a driver) one of two ways - either have a "0" key value in each table that corresponds to "N/A" in cases where it doesn't apply, or just use NULL as the value in that column, which won't violate the foreign key in place.

I'll be interested to see if any others have different suggestions, but that's how I've generally done your situation in the past, and I've used NULL for non-applicable key values.
0
 
LVL 86

Author Comment

by:CEHJ
ID: 38716930
Thanks. I did think of doing it like that but one thing in particular put me right off - the fact that the design is not extensible - at least not without ALTER TABLE and adding columns
0
 
LVL 20

Assisted Solution

by:gatorvip
gatorvip earned 165 total points
ID: 38736416
Another possibility is to add multiple tables, one for each type of additional Info you have. The key would be the order ID, or something similar to that. There are two advantages:
1. You shouldn't have to modify your existing structures
2. If you want to add more info units, you just create more tables. If at a later point you don't need those particular units anymore, you can simply delete/archive the extra tables w/o affecting anything else.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38736672
If you add a separate table for each type of detail and just use the orderid as a key, wouldn't you have to check every one of these tables whenever you want to see if something applies to an order? I think that's what the OP is trying to avoid - checking 8 different places to see if one of 8 things apply to an order, but just having an easy way to find the features of an order and maintain referential integrity.
0
 
LVL 86

Author Closing Comment

by:CEHJ
ID: 38783989
Actually folks, despite my misgivings on purist/theoretical grounds, i'm going to accept the scenario i was trying to avoid, for pragamatic reasons
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question