One-to-one relationship could usually be stored in the same table. Are there reasons not to store them in the same table?
views:
85answers:
4
+3
A:
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.
lunactic
2010-04-29 18:55:07
+3
A:
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.
Oded
2010-04-29 18:55:31
Tables that are too wide can also be slower to access.
HLGEM
2010-04-29 19:00:00
@HLGEM - Quite true. Added to answer :)
Oded
2010-04-29 19:02:40
the limit of 8060 bytes per row was an OLD SQL Server limit. this works in SQL Server 2005: `create table big_row (a varchar(5000),b varchar(5000),c varchar(5000),d varchar(5000),e varchar(5000),f varchar(5000),g varchar(5000), h varchar(max));insert into big_row values (replicate('A',5000),replicate('B',5000),replicate('C',5000),replicate('D',5000),replicate('E',5000),replicate('F',5000),replicate('G',5000),replicate('H',10000))` Your link is for SQL Server, anyway I thought the question was about MySql. However your bit about large tables (I assume you mean width, and not rows) is correct.
KM
2010-04-30 11:50:14
see this: [How Sql Server 2005 bypasses the 8KB row size limitation](http://codebetter.com/blogs/raymond.lewallen/archive/2005/12/30/136047.aspx)
KM
2010-04-30 11:53:24
+1
A:
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.
KM
2010-04-29 19:01:26
A:
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.
Chris Wallace
2010-04-29 19:08:38