4 Replies - 1294 Views - Last Post: 08 March 2012 - 08:54 PM Rate Topic: -----

#1 RudiVisser   User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1010
  • View blog
  • Posts: 3,566
  • Joined: 05-June 09

Preference to tackling problem with multi-table relationship

Posted 08 March 2012 - 09:59 AM

The title isn't very descriptive, but consider the following example. You are creating an invoice system and need to store lots of rows, each that is either an ad-hoc charge, a product, or a service.

Let's say for starters you have the following tables: Invoice, Invoice_Row, Product and Service. I don't believe actual column definitions are relevant here as you should understand how they relate if you'll be answering this :D

So, there are a few ways I can see to tackle this.

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.

How would you approach it? To be quite honest I'm leaning towards option 1 for it's simplicity whilst still remaining a somewhat integral database, as constraints can be applied to the table.

Is This A Good Question/Topic? 0
  • +

Replies To: Preference to tackling problem with multi-table relationship

#2 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6247
  • View blog
  • Posts: 24,014
  • Joined: 23-August 08

Re: Preference to tackling problem with multi-table relationship

Posted 08 March 2012 - 11:03 AM

Working with a billing system now...a not-yet-implemented third-party one and I have no specs to speak of and the task of integrating it with our product. I sorta feel your pain!

For starters, I would definitely throw #2 out. Yuck! Sounds a bit too much like the EAV antipattern for my liking.

Were I doing this, I think my first choice would be #3, just because I'm so used to avoiding using NULL fields as indicators of some logic.

So relationship-wise, an Invoice hasMany Invoice_Row, an Invoice_Row hasOne ChargeableEntity, and a ChargeableEntity belongsTo ChargeType? I think that's the way I would approach it, but perhaps baavgai or AdaHacker might be along soon with more sage advice. Although I designed the majority of the DB for our current product, I don't particularly feel it's my forte (although it has served us well to this point ;) ).
Was This Post Helpful? 1
  • +
  • -

#3 RudiVisser   User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1010
  • View blog
  • Posts: 3,566
  • Joined: 05-June 09

Re: Preference to tackling problem with multi-table relationship

Posted 08 March 2012 - 11:29 AM

Same, but at the same time wouldn't that mean that Chargeable entities would need a "Type" too, which will then link them to the Purchase? It's all the same crappy mess, unless that was handled purely at the application level.

What do you think?

EDIT: Because the more I think about it, the more I realise that Invoice_Row is essentially what chargeable is trying to be.

This post has been edited by RudiVisser: 08 March 2012 - 11:49 AM

Was This Post Helpful? 0
  • +
  • -

#4 RudiVisser   User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1010
  • View blog
  • Posts: 3,566
  • Joined: 05-June 09

Re: Preference to tackling problem with multi-table relationship

Posted 08 March 2012 - 03:09 PM

Alright, here's the design that I've finally come up with. Obviously unfinished, QuoteRow and InvoiceRow are separated because they will be distinctly different in the end, but this shows the structure I'm going for when it comes to keeping cost snapshots and still separating out the Product/Services.

Posted Image

Any suggestions?
Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi   User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: Preference to tackling problem with multi-table relationship

Posted 08 March 2012 - 08:54 PM

I can't look over your design at the moment, but I can give you a sprinkling of advice.

My work uses pattern #3 that you you defined in your OP. As far as I'm concerned, this is the right pattern to use. I didn't implement the pattern used at my work, so I'm not blowing my own trumpet here, I am bound by that pattern, and I'm willing to stand by it.

There are problems with this pattern though, when it comes to querying large datasets, in particular the metadata. JOINing becomes problematic, as you have to use CASE statements to JOIN against relevant tables to resolve the lookup information correctly. Now, I use MSSQL, and I know something like this works:
INNER JOIN LookupTables UserType ON UserType.ID = (
  CASE
    WHEN EntityType.ID = 1 THEN Admin.UserTypeID
    WHEN EntityType.ID = 2 THEN Client.UserTypeID
    -- You get the picture
  END
)


I can't speak for whether this works in MySQL, I have never tried it. Also, since resolving relational information can involve multiple conditionals in your queries, it can lead to query bloat, as well as performance hits. Smart indexing of course is the solution, but I'm just warning you.

If you're querying against a known EntityType, the problems are trivial, as you will be explicitly setting the JOINed table names, but when you start getting into dynamic meta-data, it can become a mess, often necessitating code-generated queries. In MSSQL and BIDS, this is problematic in terms of troubleshooting and optimisation. In PHP, I imagine it may be problematic with PDOs, as your query string are no longer generic, so you might lose the performance bonus of statement preparation and query reuse.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1