DB schema question, products types

Question

My question is how to model transactions for different types of products.

So there is a Transactions table:

TransactionId(pk)
Amount
Method
AuthorisedBy(fk)

And there are different tables for different types of products. Books table:

BookId(pk)
Name
Description
Price

And a Pens table:

PenId(pk)
Color
Description
Price

And a table for pencil sharpening services:

SharpenId(pk)
Description
Price

---

Now my question is linking the transactions with the particular id's of the different items. One method was to have in the transaction table:

TransactionId(pk)
Amount
Method
AuthorisedBy
ProductType
ProductTypeId(fk)

Where product type would refer to the title of the table, eg Books and the product id would refer to the BookId in that case.

OR another method would be to have a linking table of 'products' that refer to each different id of the other tables, so the Transaction table would look like this:

TransactionId(pk)
Amount
Method
AuthorisedBy
ProductID(fk)

and the products table would look like this:

ProductId(pk)
PoductType
ProductTypeId(fk)

But then this is a table that is exactly the same as the transactions table. So my question is how do I efficiently link the different product type tables to the transactions? Please note I am not modelling a school pencil sharpening service, they're just examples :P

Answer 1

Hi. First of all i dont like the DB model. You need only one table for product definition that can hold columns that describes every product: (table)Products: ID - PK, Name, Description, Color, Price, ProductTypeID - FK

You need additional table that explain the type of the product, it's category let say: (table)ProductTypes: ProductTypeID - PK, ProductTypeName

And for registering transactions you will need only one table: (table)Transactions: TransactionID, ProductID - FK, Amount, Method, AuthorizedBy

I think this schema will be OK for you to resolve your issue. Happy coding.