A Database Design Problem - Catalogue
-
I'll try to describe my problem. I'm trying to make a program that will display online the articles that are on stock in our company. All these things have completely different properties, so they cannot be put in the same table. Just to illustrate: Let's say I'm trying to combine apples, oranges, sports cars and rockets. First ones have sorts, cars have horse powers and gas engines and rockets... it doesn't matter. All these are articles/products. I my case I'm talking about resistors, capacitors, CPU s, micro controllers, LCDs, aluminum boxes, light diodes, electronic units, etc. As you see all these should belong to different product categories and each category in a separate table. And also this is my very database first project and it turned out to be much more complex than I've anticipated initially. I've searched a lot of DB examples but I cannot find any similar scenario. Please give me an idea how to approach this problem! Is it possible to use the standard approach - product table - categories tables or such relation is impossible? The only idea I have so far is to create different tables for each category and to keep them separated and the link between them to be on the application level and not on the SQL database level. :wtf: Thank you for reading all that! :-O
-
I'll try to describe my problem. I'm trying to make a program that will display online the articles that are on stock in our company. All these things have completely different properties, so they cannot be put in the same table. Just to illustrate: Let's say I'm trying to combine apples, oranges, sports cars and rockets. First ones have sorts, cars have horse powers and gas engines and rockets... it doesn't matter. All these are articles/products. I my case I'm talking about resistors, capacitors, CPU s, micro controllers, LCDs, aluminum boxes, light diodes, electronic units, etc. As you see all these should belong to different product categories and each category in a separate table. And also this is my very database first project and it turned out to be much more complex than I've anticipated initially. I've searched a lot of DB examples but I cannot find any similar scenario. Please give me an idea how to approach this problem! Is it possible to use the standard approach - product table - categories tables or such relation is impossible? The only idea I have so far is to create different tables for each category and to keep them separated and the link between them to be on the application level and not on the SQL database level. :wtf: Thank you for reading all that! :-O
I think you're falling into the trap of trying to model the 'real world'. In software systems we don't model all the properties of the real-world objects we deal with, only those properties that are significant to the problem we're trying to solve. In a retail system only a few things are significant: an identifier to uniquely identify this product, (commonly) a more user-friendly description, the price we plan to sell it at, and the level of stock we have. If you need to categorise, put a 'category' indicator on the product table. Generally you shouldn't split up a logical collection of items into multiple tables. It becomes very hard to join them back together. If for any reason you actually do need to, for example if one category is queried much more heavily than others and it would benefit from being placed on a different disk, consider the database's partitioning support (if any). I believe it was possible to simulate partitioning efficiently in SQL Server 2000 but can't remember the details. If you need to store additional information that won't be uniform across all items, consider nullable columns and using NULL where the value doesn't apply, or what I call 'extension tables' which are associated with the main, common, table in a one-to-one relationship, which stores only the extended information about this type of item.
DoEvents: Generating unexpected recursion since 1991
-
I'll try to describe my problem. I'm trying to make a program that will display online the articles that are on stock in our company. All these things have completely different properties, so they cannot be put in the same table. Just to illustrate: Let's say I'm trying to combine apples, oranges, sports cars and rockets. First ones have sorts, cars have horse powers and gas engines and rockets... it doesn't matter. All these are articles/products. I my case I'm talking about resistors, capacitors, CPU s, micro controllers, LCDs, aluminum boxes, light diodes, electronic units, etc. As you see all these should belong to different product categories and each category in a separate table. And also this is my very database first project and it turned out to be much more complex than I've anticipated initially. I've searched a lot of DB examples but I cannot find any similar scenario. Please give me an idea how to approach this problem! Is it possible to use the standard approach - product table - categories tables or such relation is impossible? The only idea I have so far is to create different tables for each category and to keep them separated and the link between them to be on the application level and not on the SQL database level. :wtf: Thank you for reading all that! :-O
You could have one table of Categories and another of CategoryProperties (multiple rows for each Category, containing CategoryId, Property Name and Value) - will that work for you?
-
You could have one table of Categories and another of CategoryProperties (multiple rows for each Category, containing CategoryId, Property Name and Value) - will that work for you?
-
I think you're falling into the trap of trying to model the 'real world'. In software systems we don't model all the properties of the real-world objects we deal with, only those properties that are significant to the problem we're trying to solve. In a retail system only a few things are significant: an identifier to uniquely identify this product, (commonly) a more user-friendly description, the price we plan to sell it at, and the level of stock we have. If you need to categorise, put a 'category' indicator on the product table. Generally you shouldn't split up a logical collection of items into multiple tables. It becomes very hard to join them back together. If for any reason you actually do need to, for example if one category is queried much more heavily than others and it would benefit from being placed on a different disk, consider the database's partitioning support (if any). I believe it was possible to simulate partitioning efficiently in SQL Server 2000 but can't remember the details. If you need to store additional information that won't be uniform across all items, consider nullable columns and using NULL where the value doesn't apply, or what I call 'extension tables' which are associated with the main, common, table in a one-to-one relationship, which stores only the extended information about this type of item.
DoEvents: Generating unexpected recursion since 1991
Thank you! What I have in mind for example is the product browser of the website http://www.farnell.com This site is huge. Mine will be very small with not that many items (compared to Farnell) and with few users. I have difficulty to accept the idea with NULLable columns yet, because because I might end with dozens and dozens if not hundreds of columns. Thank you for the remark. A friend of mine also told me: "Forget about that OOP way of thinking". I'll think about my problem a little while longer.
-
For example... Table 1: Category Fields: CategoryId (PK), CategoryName Sample data:
1 Car
2 ApplesTable 2: CategoryProperties Fields: CategoryId (FK), PropertyId (PK), PropertyName, PropertyValue Sample data:
1 1 HorsePower 1500
2 2 Sort 10Hopefully, the above clarifies my thought on this... don't know if it helps you.
-
For example... Table 1: Category Fields: CategoryId (PK), CategoryName Sample data:
1 Car
2 ApplesTable 2: CategoryProperties Fields: CategoryId (FK), PropertyId (PK), PropertyName, PropertyValue Sample data:
1 1 HorsePower 1500
2 2 Sort 10Hopefully, the above clarifies my thought on this... don't know if it helps you.