I have the following set of tables but I don't care for the design. It seems like there should be a better way of doing this. Let me explain what's going on:

-A visitor comes to our website and has the option of inputting a promotional code when buying our product. The promotional code will be related to a category, and may or may not be related to a subcategory. The promotional code may or may not be related to an affiliate.

-Should the visitor skip the entry of a promotional code, they will be presented with the opportunity to select a category, and if that category has subcategories, a subcategory as well. The category/subcategory may or may not be tied to one or many promotional codes and, in turn, any affiliates that the related promotional codes are related to.

-A category or subcategory may be related to none, one, or many promotional codes.

I've trimmed the fat off of the diagram so it's easier to read. What would you do differently? Thank you in advance for your assistance.

Attachments currentDBsimple.png 37.97 KB
6 Years
Discussion Span
Last Post by BitBlt

My $.02 worth:
Based on your description of the process the user goes through, the diagram you showed is pretty tight. Meaning, it is the minimum data structure that would support your description.

It's unfortunate that it seems clunky, but there you go. If you remove any of the relationships you would have to enforce the referential integrity in the code (always chancy). And, because PromoCategories can be assigned independently of PromoSubcategories, you can't remove the relationships between Promos and PromoCategories (or VisitorActivity and PromoCategories). Again, strictly speaking you could remove the fk relationships, but you'd have the possibility of orphaning data if the PromoCategories or -Subcategories ever changed. I wouldn't chance it, personally.

Hope this helps.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.