2

Consider we have a database that has a table, which is a record of a sale. You sell both products and services, so you also have a product and service table.

Each sale can either be a product or a service, which leaves the options for designing the database to be something like the following:

  1. Add columns for each type, ie. add Service_id and Product_id to Invoice_Row, both columns of which are nullable. If they're both null, it's an ad-hoc charge not relating to anything, but if one of them is satisfied then it is a row relating to that type.

  2. Add a weird string/id based system, for instance: Type_table, Type_id. This would be a string/varchar and integer respectively, the former would contain for example 'Service', and the latter the id within the Service table. This is obviously loose coupling and horrible, but is a way of solving it so long as you're only accessing the DB from code, as such.

  3. Abstract out the concept of "something that is chargeable" for with new tables, of which Product and Service now are an abstraction of, and on the Invoice_Row table you would link to something like ChargeableEntity_id. However, the ChargeableEntity table here would essentially be redundant as it too would need some way to link to an abstract "backend" table, which brings us all the way back around to the same problem.

Which way would you choose, or what are the other alternatives to solving this problem?

Rudi Visser
  • 21,350
  • 5
  • 71
  • 97
  • See this answer: [http://stackoverflow.com/questions/9174200/how-to-create-multiple-one-to-ones/9178524#9178524](http://stackoverflow.com/questions/9174200/how-to-create-multiple-one-to-ones/9178524#9178524) – ypercubeᵀᴹ Mar 08 '12 at 19:46
  • It may get complicated if you want to store different Sale details depending on the type of sale (Product or Service). – ypercubeᵀᴹ Mar 08 '12 at 19:47
  • @ypercube Yes that is basically my option #1, but I'm not certain if that is the right way. Like you say it may get complicated, what if a different type is added (in addition to Product/Service, yes I know it's rare)? – Rudi Visser Mar 08 '12 at 19:59
  • 1
    Hm, no I meant to link another question. See the answer of gbn here (option 3): [NULLs in a composite primary key - SQL Server](http://dba.stackexchange.com/questions/9752/nulls-in-a-composite-primary-key-sql-server/9754#9754). Those Pet/Cat/Dog are your ChargableEntity/Product/Service – ypercubeᵀᴹ Mar 08 '12 at 20:11
  • 1
    If a different type is added, you add another (subtype) table. – ypercubeᵀᴹ Mar 08 '12 at 20:11

2 Answers2

2

What you are essentially asking is how to achieve polymorphism in a relational database. There are many approaches (as you yourself demonstrate) to this problem. One solution is to use "table per class" inheritance. In this setup, there will be a parent table (akin to your "chargeable item") that contains a unique identifier and the fields that are common to both products and services. There will be two child tables, products and goods: Each will contain the unique identifier for that entity and the fields specific to it.

One benefit to this approach over others is you don't end up with one table with many nullable columns that essentially becomes a dumping ground to describe anything ("schema-less").

One downside is as your inheritance hierarchy grows, the number of joins needed to grab all the data for an entity also grows.

SuperPomodoro
  • 416
  • 2
  • 7
  • Yes I believe this is the approach that I want to take (#3), but the question remains as to how exactly it should be handled to link a chargeable item back to it's "base type"? Surely that puts us back to the same question with the only solution as #1 or #2, to supplement #3, if you see what I mean.. – Rudi Visser Mar 08 '12 at 19:58
  • Could you please explain what you mean by 'base type'? Which base type are you referring to? The way I understood it, the "chargeable item" is the base type. – SuperPomodoro Mar 08 '12 at 20:00
  • Yes yes this is what I mean, where Chargeable Item is the base type and Product/Service come off it. Perhaps I'm thinking about it in the wrong way. Perhaps the Sale should link to Chargeable Item, however then the Product will link to Chargeable Item (one->one) rather than thinking of a way to go on and link a Chargeable Item to the Product/Service. – Rudi Visser Mar 08 '12 at 20:03
  • @rudi: Exactly. With option 3, the FKs should be from Product->ChargableItem and Service->ChargableItem. – ypercubeᵀᴹ Mar 08 '12 at 20:13
  • And then, for the otehr relationship, from Sale->ChargableItem. – ypercubeᵀᴹ Mar 08 '12 at 20:14
  • Yup, mindfsck over :) Thanks @ypercube and SuperPomodoro, #3 is definitely the best way. – Rudi Visser Mar 08 '12 at 20:29
  • Yes I apologize for not being more clear. When I indicated "child tables", I was implying that products and goods point back to the base table (with FKs). – SuperPomodoro Mar 08 '12 at 20:56
  • @SuperPomodoro Yeah thanks, living off minimal sleep so things are taking a little more to register than normal ;-) You was perfectly clear! – Rudi Visser Mar 08 '12 at 21:10
1

I believe it depends on use case(s).

  1. You could put the common columns in one table and put product and service specific columns in its own tables.Here the deal is that you need to join stuff.

  2. Else if you maintain two separate tables, one for Product and another for Sale. You use application logic to determine which table to insert into. And getting all sales will essentially mean , union of getting all products and getting all sale.

I would go for approach 2 personally to avoid joins and inserting into two tables whenever a sale is made.

smk
  • 5,340
  • 5
  • 27
  • 41
  • Well I would be going with your #1 regardless, my main point of the question is how to link up these separate types of table to the "common" row. – Rudi Visser Mar 08 '12 at 20:00