In SQL / MySQL, are there reasons not to put one-t

Question

One-to-one relationship could usually be stored in the same table. Are there reasons not to store them in the same table?

Answer 1

I've done this to prevent locking/blocking, put the read heavy columns in one table the update heavy columns in another, worked like a charm. A lot of big fat update transactions were slowing down a lot of reads.

Answer 2

Number and type of columns. There is a limit on the size of the columns in a table. See here. There is a maximum of 8,060 bytes per row.

Very large tables can also affect performance and can be difficult to optimize and index well.

This is apart from keeping data the is conceptually different, apart from each other. For example, a country and currency have a 1 to 1 relationship (illustrative example, I know this is not always the case). I would still not keep them together.

Answer 3

You'll find some information about when it's useful to create one-to-one relations under http://onlamp.com/pub/a/onlamp/2001/03/20/aboutSQL.html

The most important thing is following:

The key indicator of a possible need for a one-to-one relationship is a table that contains fields that are only used for a certain subset of the records in that table.

Answer 4

One - to zero-or-one relationships are common and linked from the optional to the mandatory - the example given in http://onlamp.com/pub/a/onlamp/2001/03/20/aboutSQL.html is of this kind, not one-to-one. Type/subtype relations can be implemented like this.

one-to-one relations occur when each represents a clear, meaningful entity, which in a different context may be in some different relationship and where a minor change to the requirements may change the cardinality of the relation. It is arbitrary which links to which so its best to choose one to be optional and convert to one to zero-or-one.