Based on the information you have provided, I would choose the first model.
It is more flexible, scalable, <b>and it is normalized</b>.
Let’s talk about the second model. In the CONTEXTS table, the name column is going to refer to the context name, or the property name ? Maybe he wants to add another ‘name’ column to solve this dilemma. Since one property could be related to more than one context, this design will produce redundancy.
After applying normalization to this design, the result is the first one. This is a strong argument for me. Create a table with the second design, and insert some data in which one property appears for more than on context, and bring the resultant redundancy to your associate’s attention.
Now, let’s imagine that you want to give users the administration of this relations, by means of an application, in which the user see a list of contexts, and a list of properties, and he/she can establish relations between them. Using the second design, where are you going to get the properties list from ? and the contexts list ? what if you want to remove a relation between some context and some property (a property that has no other relation with any other context), but you still want to keep that property in you catalog, for future use with some other contexts ?
Hope this helps.
I would tend to agree with the above answer … BUT things are never as easy as they seem. I find it possible that the original description is not complete; its just too basic.
The best model to use is the one that correctly reflects the rules of the data. Thus in the strictest sense only of the the three models is right. You should ask yourself some basic modeling questions about the nature of your objects. However, it is difficult to answer you because you did not give us the necessary KEY information to adequately describe your tables. Just columns is not sufficient. I will provide my own keys and given these keys note the rules that fall out of each datamodel. You can ask yourself some basic questions based on these rules. If the question is NO then the related model does not work.
Model#2 tells us that each context relates to only one property. Is this true? If so Model#2 is correct.
add constraint context_Pk primary key (context_id).
add constraint context_UK unique (name)
Model#3 tells us that each property can relate to only one context. Is this true? If so then Model#3 is correct.
add constraint property_PK primary key (property_id)
add constraint property_UK unique (name)
Note that Model#2 and Model#3 are not interchangeable given the key constraints I have provided.
If you cannot claim either Model#2 or Model#3 then you must use Model#1. There really is not much debate to be had. If you two guys are thinkers then you should be able to figure out the answers to these questions.
If you do not accept the keys I have provided, then all your models are really just variations on Model#1 anyway except that in this case Models#2 and #3 have normalization flaws as was indicated by a prervious poster so why would you want to use them?
So get to it, ask if the keys I provided work or not and if not use Model#3.
Good luck, Kevin