Solved

Data modelling issue

Posted on 2012-12-22
5
329 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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

695 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