BO Design,Which way is better

mamboer posted on Wednesday, February 04, 2009

   I'm now modelling three business objects,which are Product,Post,and Comment. According to my business need right now,a Product may have many Comments,and so does a Post.But in the future,it's very likely another business object Album E.G.,is also commentable.To make the question simple,let's topic around Product,Post,and Comment firstly.
   As the mail's subject says,i'm not sure about the better way designing my database to fulfil my business need,i got three choices now:
   1, 4 tables, use 2 tables to maintain comments separately
      Which are Products,Posts,ProductComments,and PostComments. In the ProductComments table,ProductID is the FK. PostComments table has the same design.
2, 5 tables, make Comments independent
Which are Products,Posts,Comments,ProductComment,and PostComment. Abviously,ProductComment is the relation table, and so does PostComment.
3, 3 tables, design a general table to maintain comments
Which are Products,Posts,and ObjComments. The ObjComments table contains two special columns,ObjType and ObjID. If a comment is a product comment,the ObjType will be "Product". ObjType looks like an enum here.

   I know that the first two methods are ok,the second one maybe better than the first one,because it has the most normalization. But both have the same pain...Assumming that we need to add another commentable object Album e.g.,we have to increase our tables number accordingly. When come to the third way,we won't have the pain no longer,but is it fine?
   Because i'm not experienced,i wrote the TOPIC to you guys for your suggestions,please help.:) When you run into this design scene,which way will u choose?Or u get an even better choice?

    Sincerely,
     Levin.Van

Jack replied on Wednesday, February 04, 2009

In terms of the database - if you will have 1 to many comments then option 2 is really the most common sense especially if your comments have the same data structure.

mamboer replied on Wednesday, February 04, 2009

Yes,my comments have the same data structure.
But how about the option 3? It has many strong points,you can fetch all comments of a specified person in a single query e.g.

rsbaker0 replied on Wednesday, February 04, 2009

We have a table in our schema that is analogous to your option 3. The data is common to multiple different entities but has the same structure, so the foreign key is "variable" like yours -- the type column determines what table it goes with.

It works well with the possible shortcoming that it is not straightforward (even possible?) to define it as a foreign key in the database itself, if that is important to you.

ozitraveller replied on Wednesday, February 04, 2009

3! And you only need 1 BO to maintain it then.

mamboer replied on Wednesday, February 04, 2009

Excited to hear this

rsbaker0:
We have a table in our schema that is analogous to your option 3. The data is common to multiple different entities but has the same structure, so the foreign key is "variable" like yours -- the type column determines what table it goes with.

It works well with the possible shortcoming that it is not straightforward (even possible?) to define it as a foreign key in the database itself, if that is important to you.

Jack replied on Wednesday, February 04, 2009

You should be encapsulating your select in a procedure or a view so you can still simulate a single select statement to the front-end. Writing a sqlstatement with a union to get both sets of comments in one pass is trivial.

Keep it simple and let the database do what it does best and let it maintain the FK's, the constraints etc. There are a dozen ways you can write sql statements to get your data but as soon as you start mixing and matching and making pseudo-keys just to try and simply a single example of how you might pull the data out you will run into issues.

You can create as many versions of your stored procs or views for each use case that you have. It simplifies the middle and/or front tier.

Having the middle relationship table between the base object and the comment table will allow you the flexibility to add other data elements there to further expand the relationship. Say you want to create a link back to another comment? or enforce 1 to many in one relationship but 1 to 1 in the other.

jack

mamboer replied on Wednesday, February 04, 2009

Er,Maybe Jack is right,i read many on DB design a few hours ago,they all said that it's wise not to design general table like Objcomments.
On the other hand,we have to maintain table relationships manually when taking option 3,although which is trivial.
Thanks u guys,let's close this topic.

rfcdejong replied on Thursday, February 05, 2009

Jack:
In terms of the database - if you will have 1 to many comments then option 2 is really the most common sense especially if your comments have the same data structure.

Option 2 is really the best for performance and database integrity. Enforce foreign key constraints.

ajj3085 replied on Thursday, February 05, 2009

I agree with this. Option 2 is the best method.

If you enable comments for something else, all you'll have to do is add a new link table.

Option 3 doesn't let you set foreign key relationships; a key part of a relational database!

mamboer replied on Thursday, February 05, 2009

:)Thanks all your voices.
For the gift,i recommend u a cool rock band---The Crimea,heaha!