CODEWITHSUNDEEP
mysqldatabasestructure
TheCokeGuy
TheCokeGuy

Reputation: 51

Abstract/super/sub class-like structure for database design

I've run my head against the wall a couple of times on this one. So I'm hoping for a little help in the right direction.

I have a table with ORDERS, one with TRAINS, one with FLIGHTS and one with BUSES. Each order should have a single way of transportation. My design has up until now consisted of a field in the ORDERS table stating the type of transport (train, flight, bus) and a field containing the forreign key to the stated type of transport.

Is there any better way to do this?

Upvotes: 0

Views: 1373

Answers (2)

Uğur Gümüşhan
Uğur Gümüşhan

Reputation: 2543

You can use the same parent object Id for all items if they have object structure and subtype-supertype relation. do not treat them like different objects like the tree model. This thread shows an example

if you are using object oriented modeling, you would use the object oriented structure in the database, too. so if you are extending a class, you are also extending a data row, joining them on the same id numbers. that's why I say "use same id's for supertype and subtype, because you would want to join the supertype and subtype to create a subtype object.

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29629

The best description I've seen of this topic is in Craig Larman's book "Applying UML and patterns" - though he writes from an object oriented, rather than database point of view.

There are 3 alternatives in the relational world (this is based on the Larman book):

  • subtype per variant. So, you create a "order_flight" table with airline, seat choice etc, and a "order_train" with from_station, to_station etc. This keeps the tables nice and self-describing, but makes your SQL into a huge mess - it has to change for every subtype.
  • single table with all possible columns: in this case, you have a single table with all the possible fields for all subtypes. This way, your SQL stays far simpler - but the table becomes a huge mess, and
    you depend on your client application to "know" that flights have
    airlines, but trains don't.
  • table for common attributes with subtypes storing their unique values in their own tables. This is basically what you have chosen to date; the relationship can be set either at the "order" table, or in the subclass table.

Each option has benefits and drawbacks - especially in a situation where you don't know in advance which subtypes you're going to need, the first option is the simplest on the database end, but creates a bit of a mess for the client code.

Upvotes: 1

Related Questions