Solved

Data modelling issue

Posted on 2012-12-22
5
282 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
  • 2
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 165 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now