One to one relationship?

[C Tate]

I am still trying to design my database (keep getting given extra info which
changes things). It is all to do with sites for housing developments. Each
site will have a part of it set aside for affordable housing. There will only
be one such element on each site. And this affordable housing element will
have houses on it. As each site has only one part of it which is the
affordable housing part and there is only one affordable housing element on
each site, does this mean I have a true one to one relationship?

If so, what implications will it have for my database as I hear that such
relationships are rare.

 

[Ken Snell \(MVP\)]

The answer to your question is -- maybe.

1-to-1 relationships are not common, and usually are used when you have more
attributes for an object that can fit within the field/byte size limits of a
table, or when an attribute does not have a value very often.

From your description, I think I'd be inclined to set up a AffordableHousing
value for type of developments, and use it just the same as other types of
development. While you may have only one right now, as you're already
learning, databases have a tendency to expand/change as you use them. If you
set this up now as a 1 - to - many, you'll save hours of work changing it
from 1-to-1 later when you are told that there no longer is just one
affordable housing type per site.

 

[C Tate]

Are you saying I should set up a table for affordablehousingsite and just set
it as a one to many (form the mainsite table) anyway - just in case the
design DOES change?

 

[Ken Snell \(MVP\)]

I don't know that you need a separate table for affordablehousingsite by
itself, but I cannot say for certain because I don't know what your entire
data structure is. What I am saying is that, assuming it's one type of
development that can occur on a site, it should be created as one of the
development types, and then you can have a child table to track the
information for each of the development types on each site. And yes, I
recommend that you consider handling it this way because I cannot tell you
how many times I have been told "There will always, only, ever, be just one
of these!", only to find out a few months into the project that "OH, wait,
we will have more of these....."