7 Replies - 8994 Views - Last Post: 24 December 2012 - 09:51 AM Rate Topic: -----

#1 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2684
  • View blog
  • Posts: 11,335
  • Joined: 20-September 08

Data modelling issue

Posted 23 December 2012 - 07:15 AM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: Data modelling issue

#2 trichardson  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 9
  • Joined: 02-December 12

Re: Data modelling issue

Posted 23 December 2012 - 09:15 AM

Can you tell us the reason you don't want to have an OrderItems table? Seems to me that you can optimize your model with an OrderItems table.
Was This Post Helpful? 0
  • +
  • -

#3 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2684
  • View blog
  • Posts: 11,335
  • Joined: 20-September 08

Re: Data modelling issue

Posted 23 December 2012 - 09:46 AM

Quote

Can you tell us the reason you don't want to have an OrderItems table?
I don't mind having one but for the moment that would be redundant - there's only one item per order.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3352
  • View blog
  • Posts: 11,343
  • Joined: 12-December 12

Re: Data modelling issue

Posted 23 December 2012 - 10:05 AM

My interpretation of your description is that you have a single Orders table, and that extra information about each order will be stored in separate tables, according to the type of the order? And that there will be several tables to store this extra information, because the type of information you need to store is distinctly different for the order type?

I suggest that you do need an OrderType(or ID) in the Orders table, otherwise there is no way of determining which of the extra-info tables to reference (for a particular order). The OrderType would need to be used programmatically to construct SQL statements that link to the correct extra-info table. That is, you need to construct a 1-1 correspondence between the OrderType(ID) and the extra-info table to include in the SQL statement.

The extra-info tables don't need an Extra_Info foreign key; they would use the OrderID number instead.

The extra-info tables could include the OrderType(ID) but this would be redundant: it would be the same for the entire column, and it won't help you to build SQL statements, as it is the name of the extra-info table that you need to know.

But I may have misunderstood :)/>/>/>

This post has been edited by andrewsw: 23 December 2012 - 10:08 AM

Was This Post Helpful? 1
  • +
  • -

#5 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3352
  • View blog
  • Posts: 11,343
  • Joined: 12-December 12

Re: Data modelling issue

Posted 23 December 2012 - 10:35 AM

FWIW It would be possible to use a single (complicated!) SQL statement to extract data from all the extra-info tables, excluding those that don't match the orders' OrderType(ID). That is, all the extra-info tables would include OrderType(ID) as an extra column (repeated all the way down!).

This way lies madness though :helpsmilie:/>/>! The query would constantly run against a number of tables that it doesn't need to; and if you add an additional extra-info table you will need to revise ALL of your existing SQL statements.

The way I describe above is much simpler. You just need to use a switch statement that corresponds Orders' OrderType -> extra-info-table-name. You could even store this information in another table and use a lookup (rather than a switch that you would need to edit later) to retrieve the correct table name and insert it into the SQL.

This post has been edited by andrewsw: 23 December 2012 - 10:37 AM

Was This Post Helpful? 0
  • +
  • -

#6 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2684
  • View blog
  • Posts: 11,335
  • Joined: 20-September 08

Re: Data modelling issue

Posted 24 December 2012 - 07:54 AM

Quote

My interpretation of your description is that you have a single Orders table, and that extra information about each order will be stored in separate tables, according to the type of the order? And that there will be several tables to store this extra information, because the type of information you need to store is distinctly different for the order type?

That's it essentially

Quote

The OrderType would need to be used programmatically to construct SQL statements that link to the correct extra-info table.
Yes, that's the thing i don't like. As, in addition to one column having a foreign key into n different tables, it also means that the persistence layer of the application depends on the application layer, which seems quite wrong

Quote

You could even store this information in another table and use a lookup (rather than a switch that you would need to edit later) to retrieve the correct table name and insert it into the SQL.
That's true. I had thought of that, but it only sweetens the pill slightly and really, despite that making the design more flexible, what you have there is metadata mixing with data :(/>

This post has been edited by g00se: 24 December 2012 - 07:54 AM

Was This Post Helpful? 0
  • +
  • -

#7 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3717
  • View blog
  • Posts: 5,979
  • Joined: 08-June 10

Re: Data modelling issue

Posted 24 December 2012 - 09:35 AM

I only really see one proper "RDBMS" way to deal with this.

That is: throw out the concept of a "self-contained" order and create different items tables linked to the order. It's not unlike the method andrewsw suggested, just taken one step further. There would be no need to link each order to one specific item, as the design would allow multiple items of different types to be attached to each order. (Not that you'd actually have to do that, and you could put stricter rules regarding that in your application if you need to. It just wouldn't be enforced on the database level.)

You could create a "Vehicles" table with all the vehicles available, and a "OrderVehicles" table that links vehicles to the orders. Whatever vehicle specific data is required for that specific vehicle for that specific order could go into that link table. Then create similar tables for whatever other item types you need. - This would mean a lot more, item specific, tables, but it would give the database proper control over the whole thing, and not depend on application specific code to manage it.

I can understand not wanting this, and instead trying to build less "rigid" ways to store the data. However, no matter how you implement such a method, you will be storing details about the database structure as data inside the database, obfuscating the actual structure and putting the responsibility for maintaining it on the application code.

As such an implementation, the method andrewsw suggested isn't bad. His way, the structure of each item can be deduced based on the table. The only downside is that you need to create a "virtual" relationship that is reliant on application code to be managed.


Another interesting concept to consider here is how a NoSQL database could be used for this purpose. If we consider a document based database, like the popular MongoDB database, each "row" (or "document", as it is) is basically just a JSON object with no predefined structure. In that kind of a system, what we are discussing here is a non-issue. You'd just put whatever data is required about the object into the order document, and deal with it in the application code.

This, of course, doesn't exactly translate well into a RDBMS design, but RDBMS designs are not at all the "ideal" solution for all situations. This type of NoSQL design can be replicated inside a RDBMS with a key-value table if required. It would probably never reach higher than the 1NF, but that may well be a sacrifice worth making. Depends on what you're doing with it.
Was This Post Helpful? 0
  • +
  • -

#8 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2684
  • View blog
  • Posts: 11,335
  • Joined: 20-September 08

Re: Data modelling issue

Posted 24 December 2012 - 09:51 AM

Thanks for your input guys. I think i'm getting slightly confused here, and i'm hoping that we're not all (yes that includes me) circling around the same basic solution, with perhaps different names.
I think a bit of clarification could be in order so i'll say we can name a couple of attributes in disparate orders so that a concrete table structure can be named, along with keys and foreign keys

An order of type CLASSIC_CAR would have 'Make' and 'Model' as attributes

An order of type FIELD_STAND would have 'Brand_Name' and a list of Product (possibly de-normalized) as attributes
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1