Database Design For Multiple Product Types
-
I have a database containing different product types. Each type contains field that differs greatly with each other. The first type of product, is classified in three categories. The second type of product, is classified in three categories. But the third and the fourth one, is not classified in anything. Each product can have any number of different properties. I have a huge database,which containing about 500000 product in product table. So when I am going to fetch a product from database with all its attributes, or going to search product filtering by attributes, it makes effect on performance badly. Could any one can help me what will be the tables structure in sql or do some more indexing or any feasible solution for this problem. Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
-
I have a database containing different product types. Each type contains field that differs greatly with each other. The first type of product, is classified in three categories. The second type of product, is classified in three categories. But the third and the fourth one, is not classified in anything. Each product can have any number of different properties. I have a huge database,which containing about 500000 product in product table. So when I am going to fetch a product from database with all its attributes, or going to search product filtering by attributes, it makes effect on performance badly. Could any one can help me what will be the tables structure in sql or do some more indexing or any feasible solution for this problem. Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
Member 7702110 wrote:
Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
That's not due to a "single" thing, it requires a lot of things to be checked. 500.000 items isn't much. A normalized design (upto BCNF, see wikipedia) would, IMHO, be the best startingpoint. Next, you'd need to determine indexes. You'd also need to optimize the calls you make to the database - a rewrite of the SQL statement can often improve it's performance. ..and do check the health of database-server once in a while; is the database fragmented? Latest Service Pack installed? Enough memory?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
I have a database containing different product types. Each type contains field that differs greatly with each other. The first type of product, is classified in three categories. The second type of product, is classified in three categories. But the third and the fourth one, is not classified in anything. Each product can have any number of different properties. I have a huge database,which containing about 500000 product in product table. So when I am going to fetch a product from database with all its attributes, or going to search product filtering by attributes, it makes effect on performance badly. Could any one can help me what will be the tables structure in sql or do some more indexing or any feasible solution for this problem. Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
Consider a parent table which contains all of the fields that are common to every product. (Ex: ProductID and Description, etc) then create a child table which contains all of the varying attributes which make up the product. The structure would be something like (ProductID, AttributeName, AttributeValue) Example: 100,Color,Black 100,KosherCertified,False 100,InsuranceValue,123.99 A structure like this gives you the maximum flexibility for storing varying attributes for each one of your Products. Just an idea. :java:
-
Consider a parent table which contains all of the fields that are common to every product. (Ex: ProductID and Description, etc) then create a child table which contains all of the varying attributes which make up the product. The structure would be something like (ProductID, AttributeName, AttributeValue) Example: 100,Color,Black 100,KosherCertified,False 100,InsuranceValue,123.99 A structure like this gives you the maximum flexibility for storing varying attributes for each one of your Products. Just an idea. :java:
And AttributeType, so you can cast the attribute to the right type. And then normalize the Attribute and AttributeType into its own table. Just my $0.02
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
I have a database containing different product types. Each type contains field that differs greatly with each other. The first type of product, is classified in three categories. The second type of product, is classified in three categories. But the third and the fourth one, is not classified in anything. Each product can have any number of different properties. I have a huge database,which containing about 500000 product in product table. So when I am going to fetch a product from database with all its attributes, or going to search product filtering by attributes, it makes effect on performance badly. Could any one can help me what will be the tables structure in sql or do some more indexing or any feasible solution for this problem. Because different ecommerce sites are using this kind of database and working fine with huge different types of products.
Consider a parent table which contains all of the fields that are common to every product. (Ex: ProductID and Description, etc) then create a child table which contains all of the varying attributes which make up the product. The structure would be something like (ProductID, AttributeName, AttributeValue) how to franchise your business