Click here to Skip to main content
15,889,176 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello, good time. In asp net mvc, how can we write a repository that when the user clicks on a product... get the private information of each type of product with the common information of the products...
For example, TV...screen size...
I have defined a separate private information table for some of the products...
And I don't know how to show the information of the private and common table of products to the user in the repository


What I have tried:

I could create a table for the product characteristics... and put the product ID in this table once for each characteristic... which was not an optimal method.
Posted
Updated 1-Jul-23 21:06pm

1 solution

Assuming that you are talking about "how to store an unknown number of items that are related to a particular product in a database" then the best way is definately to store the items in a separate table, with a foreign key reference to the ID of the product itself: it's how we do it for pretty much anything.
For example, take an invoice: You have a table which contains the "one off items":
Invoices
ID
CustomerID   - foreign key to Customers table
Date
Total
Then each item they order gets a separate line in a separate table:
InvoiceLines
ID
InvoiceID  - foreign key to Invoices table
Quantity
ProductID  - foreign key to the Products table
PerItemValue
You can then assemble an invoice for a particular company using SQL JOINs:
SQL
SELECT L.Quantity, 
       L.PerItemValue, 
       P.Description,
       L.Quantity * L.PerItemValue AS LineValue
FROM Invoices I
JOIN InvoiceLines L ON L.InvoiceID = I.ID
JOIN Products P ON l.ProductID = P.ID
And you get a set of rows returned that look like this:
Results
Quantity   PerItemValue  Description          LineValue
       6          19.99  Expensive plates        119.94
       2           9.99  Saucepan                 19.98
       1         199.99  Small TV                199.99
That way, it doesn't matter how many attributes you associate with a product, or what attribute types they are.

I'd put the attribute types in another table as well, and add the appropriate foreign key to that as well.

Make sense?
 
Share this answer
 
Comments
Member 16041673 2-Jul-23 3:34am    
Thank you very much for your attention and kindness. But I have a store site where I need each type of product's unique private information to be stored in a separate table... for example, a TV has several private properties... and when a seller wants to create a refrigerator. ..must enter the private information of the refrigerator...so each type of product needs a table for private information apart from common information.
OriginalGriff 2-Jul-23 4:00am    
That wou8ld need a separate table for each *possible* product: and quite possibly each model of each type of product. And that means your DB rapidly becomes unworkable, and processing it to extract useful info becomes a nightmare.

Instead, a general "Attributes" table could be arranged to have an ID, AttributeType, and ApplicableClasses which restrict what type of product it can be applied to. For example a TV might have "PictureSize" and "HDMI ports count" while a refrigerator wouldn't but would have "Freezer compartment" and "Moveable shelves". But two different TVs from different manufacturers would have the common attributes - but one might be curved or 3D capable while the other isn't.
Setting that up as separate tables for each product is really nasty to use in practice, no matter how simple it seems to start with!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900