Quick Stupid Question, One to One Relationship Storage

On one to one relationships data will only be inserted into the second
table if that information is provided. That is it doesn't place a
bunch of null values just because you added and item to the first table
but do not define any values to the second table, there by the first
table can have thousands of rows, but the second table could have only
one item.
Correct?
Oh another dumb question. What is the advantage of one to one verses
having everything in a flat table with a bunch od spaces?
-TIA-> On one to one relationships data will only be inserted into the second
> table if that information is provided. That is it doesn't place a
> bunch of null values just because you added and item to the first table
> but do not define any values to the second table, there by the first
> table can have thousands of rows, but the second table could have only
> one item.
That's what I'd do (not add the row in the second table unless it adds
value).

> What is the advantage of one to one verses
> having everything in a flat table with a bunch od spaces?
Sometimes tables get wider than the allowed 8000-odd bytes and you HAVE to
create an extension table. If this is happening, the data may need to be
normalized instead of growing wider or just adding an extension table.
Sometimes you are adding on to a 3rd party database and don't want to (or
aren't allowed to) modify their schema.
Sometimes for performance reasons you may partition a table into separate
tables based upon the way the data is accessed (which takes a good level of
understanding of performance and your data because the gains can be lost by
potentially needing extra JOINs) so that you aren't reading these wide
tables and incurring more I/O than necessary. Though the extra I/O can
often be avoided by using covering indexes.
Sometimes your the item you are representing in a table has 200 possible
attributes but only a small subset are used for every record and the other
attributes are not used often. You can save disk space by keeping your main
table narrow and only adding records in the other table(s) when you need the
attributes. This could also help or hurt performance (see above). This can
commonly be the case when your database structure mirrors an object
hierarchy -- your "base" class is a table and you may have extension tables
for derived classes that contain extra attributes.
I'm sure there's a lot more to discuss on each of those items but that
should give you a sample of what you would use an extension table for.
Mike|||"Matthew" <MKruer@.gmail.com> wrote in message
news:1145647943.503465.33430@.e56g2000cwe.googlegroups.com...
> On one to one relationships data will only be inserted into the second
> table if that information is provided. That is it doesn't place a
> bunch of null values just because you added and item to the first table
> but do not define any values to the second table, there by the first
> table can have thousands of rows, but the second table could have only
> one item.
> Correct?
> Oh another dumb question. What is the advantage of one to one verses
> having everything in a flat table with a bunch od spaces?
> -TIA-
>
Disk usage and performance, for one thing. Partition the table so that
frequently used columns are in one table, and the rest in the other.
Narrower the table, the more rows fit on a single page, and thus less IO.
Also, extensive usage of NULLs should be avoided, if possible.
Dean|||Thanks for the replies guys. It is good to hear what I though from
someone else.
Not to get into too much detail but the reason why I wanted to make
sure is that every object for the entire DB is listed in the first
table however not every object requires extended detailed information.
I am breaking into like items or more precisely items that are grouped
together (required)|||Oh one more question about referential integrity.
If I decide to add a third table and link it to the second table that
means in order for the third table to be used, the second table need to
have the data in it? That is that unless you have the required data in
the second table, you can just make a reference form the first to third
table with out it going through the second table. .
Correct?|||Matthew,
If we're still discussing partitioning a wide table into two, or three, or
even four narrower tables (although it's a bit extreme, imho), then all
those tables should share the common primary key. IOW, both the second and
the third table should hold a refference to the first table - via the first
table's primary key.
Dean
"Matthew" <MKruer@.gmail.com> wrote in message
news:1145651871.899798.9550@.e56g2000cwe.googlegroups.com...
> Oh one more question about referential integrity.
> If I decide to add a third table and link it to the second table that
> means in order for the third table to be used, the second table need to
> have the data in it? That is that unless you have the required data in
> the second table, you can just make a reference form the first to third
> table with out it going through the second table. .
> Correct?
>