How to avoid Repetition in the Database Design?

Question

I have currently the following database structure,

            Retailers
-----------------------------------------------------------------
ID    Name    XXX    XXXX    XXXX    XXXX    XXXX    XXXX


            ProductTypes
-----------------------------------------------------------------
ID    Name    RetailerID    XXXX    XXXX    XXXX    XXXX    XXXX


            Products
-----------------------------------------------------------------
ID    Name    ProductTypeID    RetailerID    XXX    XXXX   


            Attributes
-----------------------------------------------------------------
ID    Name    ProductTypeID    RetailerID    GroupID    XXXX


            AttributesGroups
-----------------------------------------------------------------
ID    Name    ProductTypeID    RetailerID    XXXX    XXXX    XXXX



            AttributesValues
-----------------------------------------------------------------
ID    Value    RetailerID    ProductID    AttributeID    XXXX

Let say if you have a product called HP ProBook 4530s 15.6" Notebook then it may contain a Product Type called Laptop, it may belongs to Retailer1. It may contains Battery, Power, Manufacturer, Processor Name, etc attributes. May be Manufacturer and Processor are belongs to General attribute group. These attributes values are saved in AttributesValues table.

This database schema works very fine. But now I am facing an issue. Some products are needed to be completely shared between other retailers. Some products are needed to be completely shared except for few attributes between some retailers because some retailers need to show additional attributes of the same product and some retailers need to remove some attributes of the same product and some wants to update some attributes.

Currently, the above schema works with a flaw that each product(and it's related) things are repeated for retailer which uses this product. How to avoid this situation and design the database so that it works smoothly as well as no or limited repeatation.

Answer 1

Track attributes against a product (always) and optionally against a retailer. If the attibute value is used by all retailers then the retailer foreign key would be NULL.

When you join attributes to a product include an OR clause in either your join or where (depending on how you do your joining) so that you select out attribute values where the retailer FK is as given or where the retailer FK IS NULL.