A database design dilema

Question

A database design dilema

Olja Dimitrijevic

Ten Centuries

Points: 1010
More actions
September 15, 2002 at 2:03 pm

#80125

Hi,
I would greatly appreciate help on a database design dilema that I am having.
I have two tables: IndividualItems and Packages. A package can contain any number of items. All individual items and packages can be purchased. I have a table called purchasedItem which will contain both of these items. I am uncertain as to what the best way to model this is.
Solution 1 has the following fields:
purchasedItemID - identity
ItemID - refers to either individualItems table or the Packages table.
isPackage - bit
Solution 2 has the following:
purchasedItemID - identity
IndividualItemID - refers to individualItems table if the item purchased is an individual item
PackageID - refers to the Packages table if the item purchased is a package.
Maybe there is a third way of doing this which is even better.
Thanks in advance,
Suada

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119747 More actions · Answer 1

How about a packages table where even single items have a package id. That way you always purchase a package even if only one item. This would allow having an item be in multiple packages as well. I think your joins would be cleaner. Good puzzle.

Andy

http://qa.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Olja Dimitrijevic Ten Centuries Points: 1010 More actions · Answer 2

Thank you for your reply. What you have proposed could prove to be a more elegant solution, however the front end users want to have a clear distinction between the two so I would have to make that implementation completely transparent. I think that in the long run it would lead to a disparity between the database model and the business model.

For the time being I have settled for the solution 2, only because I can implement the foreign keys more easily.

Thanks,

Suada

NPeeters SSChampion Points: 12358 More actions · Answer 3

I like Andy's solution. You could even have packages containing other packages.

As for the separation between business logic and database implementation, you could solve it using the isPackage bit you proposed yourself and by implementing two views to select either individual items or packages.