Well, I understand what they do and how, I just don’t get why!
It seems to me that any time you would have this relationship between
two tables…they should be one table!
not always. it all depends.
[…] Can someone supply me with an example of a
situation where you would use this relationship where it wouldn’t be
better to just have all of the relevant columns in one table?
Let’s see if I can explain (my english is pretty bad). I find it useless
when databases are in 3NF, but not when the DBA doesn’t know about
normalization. Even said this, we had to use it at my enterprise.
we manage some engineering projects here in the database, and we used to
have the following.
one budget request <-> one budget <-> one project.
why? because a budget request can easily be in the database about a
before generating a budget, and a budget can be in the database about
three months before the project is accepted (and it’s created just after
accepted, not with the budget request). If we use null-by-default fields
the database, we perfectly can save the info and still maintain the 3NF
a 800-table database it’s very hard!!!)
But, having some null fields in these tables require:
a) a trigger/stored procedure/function which stop us to write in the
registry if we don’t fill all the registries required for the project.
Disadvantage of this is that if you want to create a new field under the
“project” section of the table, you have to modify the entire code for
table. (I already have it done!)
b) separating these using the same primary key or at least a unique key
which is also the foreign key pointing to the table before. (in the
budget->project’s case, since a project has a serial number).
As we use a lot of programming languages for different purposes/targets,
decided to separate the tables… less coding, better understanding.
Projects go with Projects, Budgets with Budgets, and we don’t mix stuff
is related but not the same.
It breaks 3NF, but the database is still consistent with its data.