Friday, March 23, 2012

Question about normalization

I have a question about normalization
basically I have an excel file with the following columns that my boss wants to store in a small database.


Brand Name, Retail Store, Location Info

Each Retail store may have one or more address. Each Retail store handles one or more brands Some brands may NOT be available in some Locations

So basically I created the following entities.

    Brand (BrandID, Brand) Company (CompanyID, Name, Type (retail, manufacturer, etc)) Cmp_Location (LocationID, Address info..., CompanyID) Location_Brand (LocationID, BrandID)

What do you guys think?

You've laid out a decent start.

I wonder if, however, it might be advantageous to Consider that a Brand may have multiple Products. Perhaps a BrandProduct table will prove useful. (And then of course, there may eventually be a need for a BrandProductsDetail table.)

The Retail Stores carry Products, and they may not carry ALL Products for a Brand.

As a minor point, I would name the Locations table something like: CompanyLocations -it will sort following the Company table in the event your project continues to grow and develop a need for more tables.

(And with large retail operations, Brand is just a sub-component of a Supplier. One Supplier may control multiple Brands.)

If you are using SQL 2005. refer to Books Online about the use of schemas. You could have a schema for Suppliers, one for Retailers, etc. That would make it both easier to use Table names that are meaningful without being complex AND keep them located together. Consider

Suppliers.Companys Suppliers.Products Suppliers.ProductDetails Suppliers.Locations Retailers.Companys Retailers.Locations Retailers.Products

No comments:

Post a Comment