Link to home
Start Free TrialLog in
Avatar of CEHJ
CEHJFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CEHJ

ASKER

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of CEHJ

ASKER

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