When to use 1 to 1 tables in databases

Let me start this out by saying I'm an开发者_如何学运维ything but a database expert. I'm working on some code written by someone else and I've noticed a lot of 1-1 relationships between tables. Here's an example:

tblPropertyRecord
   pID
   hasPhotos
   hasVideo
   comment
   headline

tblPropertyLocation
   ID
   pID
   country
   region
   city
   zip
   street_address

tblPropertyOther
   ID
   pID
   upload_path
   isApproved
   isPaid

This doesn't encompass every field for a property - there are about 35 fields in total per record, but each table has, and can only have ONE entry for each record 1 to 1 to 1 ... I guess is the way to describe it?

Whats the benefit of this over just having tblPropertyRecord with 35 fields? I understand the point of many to 1, 1 to many, many to many ... but this just seems like added confusion with no real benefit.

The tables might be 1-1 however the relationship might actually be 1 to 0..1 - they might be using the extra tables to avoid having a very sparsely populated table.

One common case would be due to legacy database support (i.e. you are extending onto the data store of an existing lecacy database where you do not want to risk breaking existing functionality by adding new columns).

If this is greenfield, and if you are 100% sure that the fields in question will always be one to one, then (generally) there's no reason not to include them in the same table. I expect there are some edge cases, like data partitioning, or optimizing your record for a specific page size, etc.

Quick quote from the Hibernate documentation on this subject

There are three cases for one-to-one associations: either the associated entities share the same primary keys values, a foreign key is held by one of the entities (note that this FK column in the database should be constrained unique to simulate one-to-one multiplicity), or a association table is used to store the link between the 2 entities (a unique constraint has to be defined on each fk to ensure the one to one multiplicity).

If the above does not hold for your scenario, then you should consider refactoring.

Edit for clarification after comments

1. If two entities share the same PK then they are effectively one entity (1:1)
2. If one of the two entities has a unique FK into the other then they are effectively one entity, but with an existence option (1:0..1) - optional extra properties
3. If an association table has unique FKs into each of the two entities then they are effecitvely one entity, but with a bi-directional existence option (0..1:0..1) - optional mix of 2 sets of properties

Hope that clears up the slightly abstract language of the quote.

Typically speaking, I break these sorts of properties into multiple tables when I have a group of properties that could be NULL under some circumstances. It's hard to tell from the schema you've posted if that's a reasonable explanation for separating this information.

Is tblPropertyRecord the parent table in all of these relationships? If so, are there ever situations where a user could create a record for a new property and not fill out part of the information contained in the other tables?

Alternatively, does your website allow users to enter some of the data on one page, before proceeding to another step and entering more information? If that's the case, then perhaps the original designer is saving information as the user progresses.

Anyway, I'd say that if neither of these conditions are true, then there isn't a really strong reason to separate the data across multiple tables.

Edit: I see your comment about the website always entering all of the fields, so it's probably safe for you to assume that this data should be in a single table.

I think it is clear that, given the info in your answers, and there are no Nullable columns, all five tables should be rolled into one table. That, however does not sound reasonable at all (35 "fields" is pretty much a read flag that you have a flat file, not Normalised). It would be good for you to inspect the real data, all use cases, thoroughly.

Designing databases for application requirements is not an acceptable reason for such poor design. If the column is 1::1 with the PK, then it must be in the table. And not, if it is not.

The only legitimate reason for splitting certain columns off into a separate 1::1 table is:

• for Nullable columns (optional or missing values)

• where the column is therefore 1::0-1 with the PK.

  (Commercial DBMS automatically place [all] BOLBs off-row, on separate devices, and allow device management; for the freeware end of town, of course you have to do all that sort of basic physical optomisation yourself.)

  (The small end of town cannot handle large rows either, so that's another commercially illegitimate reason that is legitimate in that case.)

Then you can support an Unique index on one of those columns:

CREATE TABLE Part (
    PartId,
    Description,
    EtCetera                   -- Optional SerialNo here cannot be made Unique
    )
CREATE UNIQUE CLUSTERED INDEX UC_PartId
    ON Part (PartId)

CREATE TABLE PartSerial (
    PartId,
    SerialNo,
    EtCetera
    )
CREATE UNIQUE NONCLUSTERED INDEX UC_PartId
    ON PartSerial (PartId)
CREATE UNIQUE CLUSTERED INDEX U_SerialNo
    ON PartSerial (SerialNo)               -- SerialNo can now be Unique
ALTER TABLE PartSerial ADD CONSTRAINT
    FOREIGN KEY (PartId)
    REFERENCES Part (PartId)

There is another mistake in the four tables. The ID column (and Unique Index) is totally and completely redundant. The pID is both the Primary Key and the Foreign Key to tblPropertyRecord.

1-1 tables are also one approach when modeling inheritance. From the table-names in your example it doesn't seem to be the case though.

How do you effectively model inheritance in a database?

We sometimes implement 1 to 1 tables based on modular functionality. For example, let's say we have a Person object that is used across modules. Now, some modules may need to have additional properties for that Person. Like Postal Address or Phone Numbers, whereas others don't.

This is a form of table "inheritance" in that we can expand out the functionality of a Person without impacting previous code. For us this means that the relationship is one to one or zero.

The only other reason I can think of separating out the fields like that is if the tables are big and most of the fields aren't used in queries. For example, if nearly all of the queries just take the primary table into concern, you could conceivably have the entire table cached in ram for faster response. Or, you could partition the sub tables onto different drives if data size is an issue.

However, if a great number of your queries join those tables together, then I'd consider that an absolute waste.

Number one thing in any database management scenario: Know Your Data

If you know your data, and you know you'll never have a need for more than one big table. Don't make is more complicated just because it doesn't look right or doesn't have that normalized feel to it.

Know your data and act appropriately.