• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

Data modelling issue

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
CEHJ
Asked:
CEHJ
  • 2
  • 2
2 Solutions
 
Ryan McCauleyCommented:
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
 
CEHJAuthor Commented:
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
 
gatorvipCommented:
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
 
Ryan McCauleyCommented:
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
 
CEHJAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now