Database Model for Retail Sporting Goods Company

I am a professional designer that has done some databases. I would like some feedback on this on any big mistakes I am making in the table configurations and how the PK and FK relate.

The blue boxes represe开发者_开发技巧nt data that will come from another database.

Click here to see database design

Click here to see New Design

Changed the product sizes and color table

In keeping with what Gilbert Le Blanc described, you could make this more scalable and efficient as follows:

A. Anytime you find yourself adding columns for items which represent possible user choices, consider whether they should actually be modelled as ROWS in a new table. This is referred to as "Normalization" (there's more to it than that, but for this purpose, it should cover what I'm trying to say . . .), and is key to proper database design. If you fail to normalize properly, you will experience extensive pain and regret down the road. Imagin one of your suppliers introduces a new color 6 months after you go live with your database. You will have to re-code your data-access routines just to add that color to whatever front-end presentation you are creating.

B. You MIGHT want to combine some of your Category/Sub-Category/Class Structure into one or two tables. While I don't have a concrete suggestion without knowing more about the retail biz, it seems like there may be any number of heirarchies, depending upon the product. In theory, you could actually get way with a SINGLE table for this:

**tblCategories**
CategoryID Int PK
ParentCategoryID Int FK on tblCategories CategoryID
CategoryName

Records with a ParentCategoryID > 0 are sub-categories.

I am going to attempt attaching an image (I have not done that here on SO before) of what I have just described. Caveats:

1. I Am working in SQL Server, so things might look a tad different to you.

2. I have over-simplified the model for the purpose of this example. But it does illustrate the relationships I am describing.

3. THere may be others with better suggestions for modelling the Product/Categories. The concept I have presented can be challenging to keep straight in your head, but makes use of recursive relationships to create a very flexible/scalable table structure.

I think you are on the right track. However, there are still some areas for (potentially) significant improvement in your normalization. I say potentially because I don't know enough about the sports apparel business, sizing, and the like. However, some observations:

A. I see the same entities representated in several different tables, i.e. Nike, Adidas, Etc. While I understand that one vendor may have several different brands, your table structure could make this more clear. If "Nike" is the vendor, then possible Brands of Nike might be Nike, Converse, whatever other brands Nike provides. If this is what your table does, then forgive.

B. You apparel sizing table might have some potential for additional normalization, or maybe not. Seems complex, and again, I don't know enough about the relatiosnhips represented there. I DO see what appears to be repetition of data in fields which might be better represented as rows in other tables.

C. An example of what I describe in B. is to be had with the footwear sizing. THIS can be normalized more effectively. Note that I have rather arbitrarily placed the FK for GenderCategory in tblFootwear_Sizing_Index, it MIGHT belong in tblFootwearSizes. Again, don't know enough about the footwear industry. But beyond that quibble, you will find the following arrangement more effective and manageable:

There are other areas in your model which might lend themselves to simimlar restructuring. However, as I said, it becomes hard for me to see given my lack of knowledge of your industry. I STILL think you might want to re-examine the many flavors of "Category" and "Class" Further, you most definitely should find some more descriptive names for some of those Category/Class Tables (or any table, really). Think "ProductCategory", "GenderCategory", "FootwearCategory", Etc. Also, don't be too afraid of longer table names, if the make it easier for you (or more importantly, your successor four years form now) to discern what is going on in your code. It may be more cumbersome to type now, but 6 months after you go live, and you are trying to figure out why one of your queries is not returning as expected, you will be glad you did. After all, you can always alias the table names in general use.

I strongly recommend checking out some info on database normalization, then try to apply it to your model. Getting the back-end db model right from jump can make or break your application. Here is one of many articles I got back by googling "Database Normalization":

http://databases.about.com/od/specificproducts/a/3nf.htm

This article is focused on the Third Normal Form (3NF), but provides links to 1NF and 2NF, which are pre-requisites for 3NF.

You should always strive for a minimuim of 3NF in a database design.

Hope that helps, and I would love to hear how you progress on this.

You have 2 footwear size tables.

Taking the apparel size table as an example, you get more flexibility if you make the size one of the columns.

apparelSizeId  Size   Sort order
1              M      1
1              L      2
1              XL     3  
2              S      1
2              M      2
2              L      3

With this type of table design, it's easier to add new sizes.

You can also combine a lot of your size and style tables into one table, although it does make the design harder for business types to understand.