database modeling suggestion
Here is a part of my design layout so far:
SalesOrders
-----------
OrderNumber (PK)
OrderDate
SerialNumbers
-------------
SerialNumber (PK)
ProductID (FK)
OrderNumber (FK)
Products
--------
ProductID (PK)
ProductName
IsSerialized (boolean/bit)
Here is the scenario:
We have two products (A and B). Product A is a serialized product, and Product B does not.
So, when a customer orders 1 of Product A, the product is given a unique serial number that is associated only with that particular unit. Now, if someone orders Product B, this product has no serial number. For Product A, the customer could order 1 or 100, and it wouldn't be an issue since each has their own unique serial number.
So, if someone orders a quantity of 3 of Product A, we could see the rows as:
Product ID, SerialNumber, OrderNumber
------------------------------------
A, 100, 100000
A, 101, 100000
A, 102, 100000
This is fine since each item has its own unique serial number.... but the problem I'm trying to figure out is what is someone orders more than one of Product B. Since there is no serial number, and if someone orders 3 of product B for example, there would simply be 3 rows of identical data, which the database engine would not really allow, unless I didn't have a primary key at all (which won't work either, I don't think). The key is that I want to try to develop a scheme so that both types of products can be stored in the SerialNumbers table without duplication (or minimal duplication) especially for Product B. Should I maybe add a quantity field that is only required for product B, and then have the application enforce the quantity only for Product B (since Product A can only have a quantity of 1 PER EACH SERIAL NUMBER, since the s/n is unique).
Any suggestions on how I should handle this scenario? If you need more clarification, please let me know.