1 to many vs. many to many?

searain

if tb1 and tb2 is "many to many" relationship, i will use tb3 to hold the tb1 and tb2 keys to set up the link between tb1 and tb2.

but if tb1 and tb2 is "1 to many", I should just use one field in tb2 to hold tb1's key, right? the database structure will be much simple. specially for 2 layers or more layers of "1 to many" relationship, for example category->sub-category->products.

but if for very rare cases, that someone want to assign certain products to several sub-categories, such as if he want to assign children sport shoes both to sports sub-cateogry and children shoes sub-category, then it breaks the "1 to many" relation, make it to "many to many" relation.

my questions are if 98% it is "1 to many" relations, and only 2% "many to many" relations. Should I design the database as "many to many" or should I design it as "1 to many" but handle the rest 2% specially, such as use a special tb3 only handle these 2% "many to many" relation.

Thanks!

eoghain

My personal belief is to design your database for what it will and can be used for. If you have the ability to put a product into many different sub-categories then your users will use this ability and eventually you'll have more products in multiple categories than just the occasional products you are currently thinking of.

Also when you build anything do your best to stay away from phrases like "handle the rest 2% specially". This is a sure sign that there is something wrong with your design.

Since a "many to many" design could easily be used to store "1 to 1" or "1 to many" relationships I'd recommend that you build things that way and just let your users decide how they are going to organize there products. If you build a special case to handle "kids sports shoes" today what are you going to do tomorrow to handle "womens sports shoes" or the next week for "mens sports shoes". You don't want to have to keep implementing 1-off changes and additions to your code. You'd rather be working on new features not fixing design flaws that could easily have been avoided by doing just a little more work in the beginning.

Anyway that's just my 2 cents. I'd always rather take the extra time in the beginning than trying to root through the code to fix things later when it's in production.

searain

I was thinking about something like files mangement in the UNIX system.

Folders->Sub-folders->files as "1 to many" just like the database structure for the products catalog category->sub-category->product (1 to many)

So the major database structure is "1 to many".

for the special cases, a product could be assigned to other sub-categories, just add some links like the symbolic link for the file in the UNIX file system.

Well I think here I use "many to many" again.

So instead of make the database to two parts "1 to many" and "many to many", why just one part "many to many". OK, i think you are right. Thanks!

I have been on this issues on and off before, you may have helped me before too. I designed my past projects as "many to many", but due to my clients are small business and selling specialized products, they don't use the "many to many" features even it is there. So I have second thoughts here. But well, you are right. Leave it for the clients to decider whether it is useful or not.