I have a situation that I can only describe by example. I tend to think this must be a common problem in a relational model. This problem exists in a pretty critical area of my application, so I want to make sure I make the right decision.

Imagine a basic order application for a company that sells both products and services. Products and services need to be stored in different tables, because they each have their own attributes, and they each relate to different tables. But they both need (?) to be referenced in the OrderLines table.

Customer

CustomerId

Order

OrderId CustomerId

Product

ProductId ProductTypeId etc

Service

ServiceId ServiceTypeId etc

Now each order will need to have one or many OrderLine records. Each orderline row can either be a service OR a product.

I hate this practice:

OrderLine

OrderLineId ProductOrServiceId (or "ObjectId")

I'm also not a fan of:

OrderLine

OrderLineId ProductId (nullable) ServiceId (nullable) (one or the other must be null, and one must be non-null)

I could do this:

OrderLine

OrderLineId OrderLineTypeId

OrderLineType

OrderLineTypeId

Service

ServiceId OrderLineTypeId

Product

ProductId OrderLineTypeId

This would allow me to tie everything together, but I think it just moves the problem of a relationship going in or direction or another to the OrderLineType table.

Again, this must be a relatively common problem. Any opinions on how to handle this sort of thing?

This seems to be a pretty strong case for inheritance. With the inheritance you could have

OrderLine

OrderLineId (PK) OrderId Price Quantity ... All other like fields

Then have

OrderLineService

OrderLineId (PK) ServiceId ... Any Service specific orderline information

OrderLineProduct

OrderLineId (PK) ProductId ... Any Product specific orderline information

Now you create a OrderLine OrderLineService relationship and a OrderLine OrderLineProduct relationship. With this start you can look further into what is available using inheritance in LLBLGen.

I didn't really intend it as an LLBL-related question. I need to build the model to be LLBL-independent (even though we will be using LLBL on this project).

With your suggestion (and all of my ideas so far), it's difficult to get all the details about the order lines tables in a single query. You would have to do left joins to the OrderLinesService and OrderLinesProduct tables and then check for null (probably in the app layer).

But maybe this is a problem that has no perfect solution. I was hoping I was missing something obvious.

Thanks for your suggestion.

Phil

I didn't really intend it as an LLBL-related question

What Brian was suggesting is indeed the solution of your problem in general. And you can use it regardeless of the use of LLBLGen Pro.

All you need to do is to implement the following schema:

Customer
--------------
CustomerId

Order
-----------------
OrderId
CustomerId

Product
-------------------
ProductId
ProductTypeId
etc

Service
-------------------
ServiceId
ServiceTypeId
etc

ProductLineItem
-------------------
OrderLineItemId(PK & FK to OrderLineItem)
ProductId
etc

ServiceLineItem
-------------------
OrderLineItemId(PK & FK to OrderLineItem)
ServiceId
etc

OrderLineItem
----------------------
OrderLineItemId
etc

OrderLine
----------------------
OrderLineId
OrderLineItemId
etc

Note the relation between OrderLine and Product is (1:1) And the same goes for the Service.

Walaa wrote:

What Brian was suggesting is indeed the solution of your problem in general. And you can use it regardeless of the use of LLBLGen Pro.

[...]

Note the relation between OrderLine and Product is (1:1) And the same goes for the Service.

I think the main thing I am trying to avoid is having to write a basic retrieval query that relies on left joins and/or procedural logic. My thinking now is that meeting that requirement is logically impossible in this case--maybe it's simply not a reasonable requirement. Maybe pulling the product or service information will require two separate queries, and maybe it should in a properly normalized model.

Having a foreign key that can point to two or more different primary keys (e.g. "ObjectId") presents the same problem, and this has always been my main objection to doing so (ok, you also lose referential integrity ).

So, maybe I'm just thinking about this all wrong, or trying to make a shortcut where there shouldn't be one.

Thanks for your responses--any additional thoughts would be appreciated.

(Also, I didn't mean to imply that Brian's reply wasn't useful!)

Phil

Don't worry Phil I didn't take it as such . I've posted enough to know that message. I know how difficult the issue you are going through is. It just seems that none of the answers feel like the correct way to do things.

I know to me the idea of inheritance in a data model seemed a bit foreign when I first started reading about it, but now I definitely feel that it is the best answer for certain scenarios. If anyone else has something that they do as an alternative I would love to hear it too.

You may just setup a test scenario like this in a db and try it out with llblgen to see if you get the hang of it and just how easy and powerful it is.

Walaa wrote:

Note the relation between OrderLine and Product is (1:1) And the same goes for the Service.

You could also leave out the OrderLineType table. The relation between OrderLine and ProductLineItem will still be (1:1) because of the PK on ProductLineItem.

ProductLineItem
-------------------
OrderLineItemId(PK & FK to OrderLine)
ProductId
etc

ServiceLineItem
-------------------
OrderLineItemId(PK & FK to OrderLine)
ServiceId
etc

OrderLine
----------------------
OrderLineId
OrderLineItemId
etc

Or am i missing something?

wvnoort said:

You could also leave out the OrderLineType table. The relation between OrderLine and ProductLineItem will still be (1:1) because of the PK on ProductLineItem.

Exactly that's why I left it out. It has no use.

psandler said:

Having a foreign key that can point to two or more different primary keys (e.g. "ObjectId") presents the same problem, and this has always been my main objection to doing so (ok, you also lose referential integrity ).

I guess I was not that clear. ProductLineItem & ServiceLineItem both are pointing out to the OrderLineItem. So OrderLineItem is the PK side, and both of them are the FK side of the story. So the refrential integrity is well preserved here.

Order
-----------------
OrderId
CustomerId

OrderLineItem
----------------------
OrderLineItemId (PK)
OrderId (FK)
etc

ProductLineItem
-------------------
OrderLineItemId(PK & FK to OrderLineItem)
ProductId
etc

ServiceLineItem
-------------------
OrderLineItemId(PK & FK to OrderLineItem)
ServiceId
etc

Product
-------------------
ProductId
ProductTypeId
etc

Service
-------------------
ServiceId
ServiceTypeId
etc

wvnoort: I guess you meant the OrederLine table has no meening after using the OrderLineItem. I'm all mixed up with the Order OrderLine ....OrderLineItem thing

But I guess the above modified schema will do.

Walaa wrote:

wvnoort: I guess you meant the OrederLine table has no meening after using the OrderLineItem. I'm all mixed up with the Order OrderLine ....OrderLineItem thing

You're right. I messed up the names too: Where i said OrderLineType, i should have said: OrderLineItem.