Doubt regarding a database design

Question

I have a doubt regarding a database design, suppose a finance/stock software

in the software, the user will be able to create orders, those orders may contain company products or third-party products

typical product table:

PRIMARY KEY INT productId  
KEY INT productcatId
KEY INT supplierId
VARCHAR(20) name
TEXT description
...

but i also need some more details in the company products like:

INT instock  
DATETIME laststockupdate  
...

The question is, how should i store the data? I'm thinking in 2 options:

1 -
Have both company and third-party, products in a single table,
some columns will not be used by third-party products
identify the company products are identified by a supplier id

2 -
Have the company products and third-party in separated tables

3 - [new, thanks RibaldEddie]
Have a single product table,
company products have additional info in a separated table

Thanks in advance!

Answer 1

one table for products with a foreign key to the Vendor table; include your own company in the Vendor table

the Stock table can then be used to store information about stock levels for any product, not just yours

Note that you need the Stock table anyway, this just make the DB model more company-agnostic - so if you ever need to store stock level information about third-party products, there's no DB change required

Answer 2

To be honest, I think the choice of #1 or #2 are completely dependent upon some other factors (I can only thing of 2 at the moment):

1. How much data is expected (affecting speed of queries)
2. Is scalability going to be a concern anywhere in the near future (I'd guess within 5 years)

If you did go with a single table for all inventory, then later decided to split them, you can. You suggested a supplier identifier of some sort. List suppliers in a table (your company included) with keys to your inventory. Then it really won't matter.

As far as UNION goes, it's been a while since I've written raw Sql - so I'm not sure if UNION is the correct syntax. However, I do know that you can pull data from multiple tables. Actually just found this: Retrieving Data from Multiple Tables with Sql Joins

Answer 3

You didn't mention anything about needing to store separate bits of Vendor information, just that a type of product has extra information. So, you could have one products table and an InHouseProductDetails table that has a productId foreign key back to the products table that stores the company specific information. Then when you run your queries you can join the products table to the details table.

The benefit is that you don't have to have NULLable columns in the products table, so your data is safer from corruption and you don't have to store the products themselves in two separate tables.

Oooo go with 3! 3 is the best!

Answer 4

I would advice on using point #1. What happens when another supplier comes along? It's also more easy to extend on one product table/produst class.

Answer 5

Take into account the testing of your application also. Having all data in one table raises the possible requirement of testing both the 3rd Party & Company elements of your app for any change to either.

If you're happy that your Unit test would cover this off its not so much of a worry... if you're relying on a human tester then it becomes more of an issue when sizing the impact of changes.

Personally I'd go for the one products table with common details and separate tables for the 3rd party & Company specifics.

Answer 6

I agree with RibaldEddie. Just one thing to add: put a unique constraint on that foreign key in your InHouseProductDetails table. That'll enforce that it's a one-to-one relationship between the two tables, so you don't accidently end up with two InHouseProductDetails records for one product (maybe from some dataload gone awry or something)

Constraints are like defensive driving; they help prevent the unexpected...