I’ve read the book by Kevin Yank as many of you have so I know enough to get me started on the basics. So, i thought I would take on a challenge and build a website as a learning project similar to gasbuddy.com. It will consist of many ’ gas stations’ with several products per station and each station may have a different price per product. So, I could potentially have thousands of stations with each station having a unique price per product. As you might have guessed that would mean, potentially, many thousands of different prices.
I thought I would structure it this way: A table named “Station” with the station name and address and other such identifying information. A table named “Product” with the product information such as regular, unleaded, etc… and a lookup table named “Prices” that has the prices attached. It would have 3 columns. For example Station.SID 55 and Product.PID 4 would have a price of $2.58.
I wonder if the Product table should be split into three tables, one for each product. Product-Regular, Product-Unleaded, etc. Then you can add as many tables as needed for each product type, such as Product-Diesel, etc. Not every station will have all the products, so by using only one Product table, you’ll end up with many empty fields.
That makes no sense at all. In this case there are only 3 products, but if there were - as in most real life examples - hundreds of products, would you really suggest having hundreds of product tables? I’m sure you wouldn’t, so why have 3 separate tables when there are 3 products?
How so? The products are associated to the stations via the LUT. The products are not referenced in the stations table, nor the stations referenced in the products table.
So where a product is not available at a certain station, a row in the LUT will simply not exist.