Should I model with an association table for future flexibility?

This is a database modeling question.

I normally model one-to-many with a standard parent-child table setup, and I normally model many-to-many with an association table between the 2 tables. In this one case, the current requirement calls for a one-to-many relationship. But the client was talking about some potential future requirements that would call for a many-to-many relationship.

So here are 2 implementation options:

1. Model the database as one-to-many to meet current requirements. If future requirements call for many-to-many, t开发者_StackOverflowhen I would need to change the database structure and the application code.
2. Model the database as many-to-many and have the application code limit the data to be one-to-many. If future requirements call for many-to-many, then I would just need to change the application code.

Changing database structure after the fact can be a bit of a pain in our application, but I might be introducing unnecessary complexity in the name of flexibility.

Which option would you choose and why?

It's pretty hard to design software based on potential requirements. I'd stick to actual requirements. That way you'll get done on time.

And, if you have an actual requirement for a many-to-many later, then implement it later.

Just my opinion .. on the other hand, I get paid by the hour.

Before you make the design choice, sit down with the client and ask them how serious they are about the many2many future requirement. Explain how this will change the data model later and what it will cost and see if they want to go ahead now with the many2many model.

REmemeber you can always change the model later and then make sure stuff doesn't break by taking the child table (renamed) and association table tables that replace the current child table table and putting them in a view that is named like the current child table. Then your code change is much less painful.

Agree with Seth. We had the same situation as you, and went with a many-to-many design to store one-to-many data, against a future date when we'd need to store many to many. Over time it mutated into (IMHO) a hideous kludge, as what hindsight shows to have been "individual" many-to-many relationship requirements came in and (for generally reasonable reasons) quick work-arounds were implemented. Now that we've reached the point where we have to build it out, not only do we have to refactor the system as we planned, we have to unravel and account for all the interim kludges as well.

From this question on AskTom:

Q: The consultants want to use a join table when there are many to many relationships between 2 tables. ... They also want to use exactly the same concept to represent 1 to many (parent/children) relationships. They say that having a third table has many benefits when using todays programming tools.

To me it seems that having to maintain three tables and their indexes, rather that just 2, is unnecessary, and adds overhead.

A: For many to many, you have to use an association table as they are known. This is standard.

For a one to many -- they are "smoking something funny".

Not only does it suffer from the problem you describe, but, it makes it infinitely harder to maintain that 1 to many relationship -- you need yet another additional unique index on the association table in order to enforce the one'ness.

You incurr additional IO overhead in each and every join as well -- as much as:

• n IO's to index access association table
• 1 IO to read association table

You could be adding 2-4 IO's nominally to each row joined. For no reason.

I've never heard of anyone using this as a standard operating procedure before in my life. It sounds like a really bad idea.

An example Visa Applicants: Visa-issueing department accepts many applicants. If an applicant applies for a kind of visa, he/she cannot apply for multiple types of visa.

This relationship is one-to-many (Visa-Applicants) - visa-table (visa_type), applicant-table(applicant-id, visa-type).

If we changed the rule to many-to-many, i.e. applicant could apply to multiple kinds of visas, we have - visa-table (same as before), applicant-table (applicant-id), applicant-visa-table (applicant-id, visa-id).

So the level of disruption is (if/when that biz rule change happens in the future) : applicant.visa_type column has to be migrated to applicant-visa table. Your web-page that exposes this information will also be disrupted (i.e. force to change). So your database change will be accompanied by app-changes.

In summary, I am wondering if its worth building abstractions in the DB, if that does not go hand-in-hand with application code. Meaning, if and when the biz rule changes, I'd be willing to tackle the change on the DB-layer and the web-layer together. Someone looking at the many-to-many relationship in the DB-level maybe misled in thinking that such a functionality is currently implemented.

Option 1.

• KISS
• YAGNI