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.
| Author |
Topic |
|
token
Posting Yak Master
133 Posts |
 Posted - 2005-11-09 : 16:55:13
|
| sorry to be unloading so many problems on this forum, but your expertise is invaluable to me.I have a database design problem to do with products I hold in stock and their serial numbers.Lets say I have 10 laptops in stock. Each one has a serial number, so 10 rows are occupied in my PRODUCTS table. When a user vists my webpage and clicks to see more info on a laptop, the ProductID is passed as a URL variable (e.g. results.cfm?ProductID=1) for the results page to display info on the laptop.That all sounds good enough. But what happens if all my laptops are sold. The PRODUCTS table will then be empty of the laptop and a user cannot view info on the laptop. It doesnt matter if the laptop isnt in stock, we can just order more... but I always want the user to see info on the laptop if they want.So I thought I'll ignore the serial number in the PRODUCTS table, and instead create a cell named Stock. So for 10 laptops there will be 1 row in the PRODUCTS table with details on the laptop and a cell that holds the Stock number for that laptop (i.e. 10).Sounds good so far. Now whenever someone places an order for a laptop I will then at that point in time enter the serial number for the product they ordered in an ORDER-DETAILS table. I will then reduce the Stock number in the PRODUCTS table by 1. This solution should work because even if the Stock number in the PRODUCTS table reduces to zero, the user can still see the info on the laptop (even though theres none in stock).The problem now comes when a user buys more than 1 of the same laptop. So in the ORDER-DETAILS table it will have cells for OrderID, OrderDate, CustomerID, ProductID, SerialNo, Quantity. There is no way that two laptops can have the same serial number so this solution won't work either.Any ideas out there? If you guys solve this and I finally get this thing done, I'll donate the profits from my first sale to this forum I swear to God! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-09 : 17:09:50
|
| If PRODUCTS refers to specific units with their unique serial numbers, then what you need is a PRODUCTMODELS table to store the information regarding any type of laptop or other product. The PRODUCTMODEL record and its associated information would be available for querying regardless of whether you actual had an inventory of the item in your PRODUCTS table. |
 |
|
|
token
Posting Yak Master
133 Posts |
Posted - 2005-11-11 : 10:29:28
|
| That sounds like a good idea. But how do I allocate specific units with their serial numbers to customers?For example, a customer clicks to see more info on a laptop with ProductID 1. So they are taken to the results page through a URL variable (e.g. results.cfm?ProductID=1).They then purchase 5 laptops. So in my database, I have an ORDERDETAILS table that stores info on which laptop they wanted (i.e. the ProductID) and how many they wanted (i.e. Qantity). So when it comes to making the invoice it will say something like this:Item | UnitPrice | Qty=======================================Acer Ferrari Laptop £1000 5Instead I want it to show it like this:ID | Item | UnitPrice======================================11 Acer Ferrari Laptop £1000 22 Acer Ferrari Laptop £1000 33 Acer Ferrari Laptop £100044 Acer Ferrari Laptop £1000 55 Acer Ferrari Laptop £1000 The numbers in the ID part are just there for example. They are likely to be abbreviated serial numbers I guess. I need the invoice to be set out like this so that if someone returns a product, I can then enter in the serial number and find out who bought it (and when).I thought making an online shop was easy, but its not!! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-12 : 22:30:40
|
You are really mixing up things here.  Think about your design from an entity perspective. A product is just that. It should probably be the list of products your store has, does, and will contain. The information attached to the product table should only be specific to the generic product. Have a inventory table that allows you to place product SKUs (generally, this will provide a natural product key, although you have to be careful with this) in combination with attributes of the specific inventory product such as serial number, date of placement in inventory, etc. You might have several tables like this, so you can enforce business rules and properly define your entities. An invoice should probably contain a link to the customer table is you have one, and an inventory table. You can then track which products moved out of inventory on which particular order. There might be a purchase_order table to track the request of certain products. This might tie to the product table. A process would determine what's available in inventory, reserve inventory for approved POs, and create new purchase request for products not available. It can get quite complicated. You need to document the full set of business needs and then model with a full picture in mind.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
database design confusion!