Design Issue w/ Multiple Product/Service tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Design Issue w/ Multiple Product/Service tables

Author

Topic

mgoomba
Starting Member

10 Posts

Posted - 2006-10-01 : 03:48:11

I've come up with an interesting problem that i would like to share for feedback and ideas.

I am building a database where one part of it kind of models after a cable/satellite tv service. For example, the customer has various plans they can subscribe to (ie Americas Top 120, Americas Top 180, America's Everything Pak), plus they can add addional services (ie, HBO, Sports Pack, HDTV Pak), then during their subscription they have the ability to make single purchases (ie PPV) and this extends even further where they could buy movies and have the dvd's shipped to their home.

There are 4 somewhat different products/services offered, and thus would make sense to keep a seperate table for each.

tblSubscriptionPlans (sub_id, description, price, rebill_price, bill_cycle, num_channels)

tblAdditionalServices (service_id, description, price)

tblPPV (ppv_id, description, price, num_movies, exp_date)
explanation: the user can purchase PPV movies in say increments of 3, 6 and 9 movies, can be redeemed anytime but must be used within the expiration date.

tblInventory (inv_id, movie_id, price, format, num_in_stock)

Now the fun part
In a simple e-commerce database one would probably have something like: tblProducts, tblProducts_tblOrders, tblOrders. This obviously won't work for my situation since i have many different groups of products. When a customer purchases any combinations of these products/services, whats the most logical way that i could refernece all these items as an order?

Thanks in advance for any ideas!!

Kristen
Test

22859 Posts

Posted - 2006-10-01 : 05:20:25

We have a situation where we have different kinds of products.

We have a Products table - EVERY product is in that. It contains a brief description, plus some flags for attributes like Active/Inactive, whether "stocked", "Service", "Back ordering permitted", "Customisation required" etc. but importantly a column for the "Kind" of product.

So its the data Common to all products, although I would have to admit that some of the Boolean attributes are probably more related to only certain Kinds of product, but it is more convenient for us to have them in the Products table, avoiding loads of joins to tables just to try to get the odd occasionally-needed flag.

And then we have additional tables, so a given "Kind" of product can have its own associated table of additional attributes.

This way to get the Description of a product, in a simple listing, only takes one join - rather than several optimistic outer joins!

But to calculate Price / Eligibility / etc. it is often necessary to join the additional table(s) required by the Kind of the product.

Kristen

mgoomba
Starting Member

10 Posts

Posted - 2006-10-01 : 16:04:40

Hmm, if i understand your setup correctly, doing this you wouldnt have any relationships. How are you connecting the Products table with your ProductKinds tables?

tblProducts (productID[PK], description, price, inStock, active, productTypeID[FK])
tblProductTypes (productTypeID[PK], productTypeName)
tblDVDs (dvdID[PK], productID[FK], description, some_other_attributes)
tblElectronics (elecID[PK], productID[FK], description, some_other_attributes)

Are you controlling your Identifying Keys in such a way that each one is unique across all productType tables? And thus if you Joined all your productType tables, each would have a unique "product id" that would correspond with the tblProducts productID?

Kristen
Test

22859 Posts

Posted - 2006-10-02 : 07:01:13

"How are you connecting the Products table with your ProductKinds tables"

Every Product has a ProductKindCode. Must exist in ProductKind lookup table.


SELECT *
FROM tblProducts AS P
     JOIN tblProductTypes AS PT
         ON PT.ProductTypeCode = P.ProductTypeCode

"Are you controlling your Identifying Keys in such a way that each one is unique across all productType tables"

Lets say we had a table called ProductService that provided properties of a Service-type product. The PK on ProductService would be the same as the main Product table.

A ProductService must have a valid Product record, but not all Products need to have a productService records, and if they do only one, per product, can exist.

So you could do


SELECT *
FROM tblProducts AS P
     LEFT OUTER JOIN tblProductServices AS PS
         ON PS.ProductCode = P.ProductCode
     LEFT OUTER JOIN tblProductOtherAttribute AS POA
         ON POA.ProductCode = P.ProductCode
     ...

and get all the additional Attribute data for a product.

Which I'm pretty sure is what you are saying, but I thought I'd just "say it back" in case it raises some other issue!

Kristen

mgoomba
Starting Member

10 Posts

Posted - 2006-10-02 : 14:48:03

Your saying that you include the productCode in every product table? Every product table would include a column with repetitve information? For example, the DVD producCode is 4, there are 9000 records in tblDVDs, then youll have 9000 rows with the productCode 4. This seems like a waste of space and bad practice. Or have i not understood you?

If I understand your database logic, each product table (tblDVDs, tblElec) both have unique productIDs. So if you were to join the two, productID would stay unqiue. And you control the productID numbers with tblProducts. Is this correct?

If im wrong, please share your data structures so i can understand your logic. =) You've been a great help and has made me think some more.

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 01:25:43

Sorry, I'll try to clarify:

Say we have two product types : DVD and Electricals.

Say we stock 9,000 DVD products, and 1,000 Electricals

tblProducts has 10,000 rows
tblProductTypes has 2 rows ('DVD' and 'Electrical')
tblDVDs has 9,000 rows
tblElec has 1,000 rows

tblProducts only stores information common to all products (e.g. Name, IsActive and so on)

tblProducts enforces uniqueness in ProductID (i.e. without having to check all the tblDVDs, tblElec tables)

Note that to have an extra Product Attribute table (tblDVDs and tblElec in this example) the products must require different attribute data to be stored.

So I would have one table for Stocked Products that have no special requirements - you can almost certainly put DVDs, iPODs, Washing machines, and so on in there. Then Subscription Plans and other types of services would need their own attribute table(s).

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources