Database Design - Should one-to-one relationships be avoided?

Question

Possible Duplicate:
Is there ever a time where using a database 1:1 relationship makes sense?

For the sake of simplicity, I'll ask the question straight out: should one-to-one relationships in database design be avoided or this acceptable?

I know all of the attributes of this "item" can be all hosted in ONE table, but I feel when converting my database design into business objects via an ORM, it clutters the entity with unnecessary properties.

Via the UI, hopefully this will paint a better picture, I have a main form with all of the necessary attributes. I will have a button that will allow the user to click on it and it will bring up a new form to attach extra attributes. No more than 1 entry can be affiliated with the main form (entity), i.e. it is a 0..1 end relationship.

Any advice will be appreciated.

Answer 1

I would avoid one-to-one. If there isn't a technical need for it there isn't a point. You are just creating extra joins for the db and extra tables and indexes to manage. Also, just because your table has all the fields doesn't mean your object has to.

Answer 2

No, a 1:1 relationship can totally make sense.

Imagine an entity that optionally has a bucket full of attributes - some of your entities have those, others don't.

You can either include all those attributes as columns into your entity table - but in that case, lots of columns would end up empty for a significant number of the entries.

Or: you can put those "optional" attributes into a separate table, set up a 1:1 (or rather: 0:1) relationship with the base entity table, and only store stuff in there if your entity in question really has those attributes.

The main criteria to decide whether to "outsource" some attributes into a separate table would be:

• how many attributes does this concern? If it's just one or two - don't go to lengths to put these in separate tables. But if you're talking about 8, 10, 15 - then consider it

• how many of the base entities might have those optional attributes? Again: if 95% of all entities will always have all those attributes anyway, then it doesn't make sense to do this extra step. If only half or less of your entities will have those attributes -> I would definitely consider such a table

Answer 3

Depends on application requirements

Typically, I would say that one-to-one relationships are modelled as columns in the table, there are however circumstances when this is too restrictive:

1. Your schema changes often, and the application can customise the attributes of an object
2. Performance isn't a concern and you are using views to create data layouts for abstract backend attribute storage

I have seen tables where 1->1 relationships are split across tables in vertical sharding and databases with heavy index requirements.

You can split and abstract to the point that you end up with something along the lines of an Entity-attribute-value structure .. which isn't always what you want (added complexity, performance) unless your application demands it. As marc_s says, you want to avoid this where possible.

Answer 4

If all items will have all attributes, having one table generally makes sense.

If some items will only have some of the attributes, having multiple tables makes sense.

To make ORM more efficient, something like lazy attribute fetching might come in handy. Thing is, it's still fairly rare; don't worry about optimization unless you really think it'll be an issue. Premature optimization isn't an efficient place to spend your time, by definition.

Answer 5

There are two scenarios in which it may make sense:

• A chunk of optional attributes that may be associated with a primary entity, which makes it a 1:[0-1] relationship. This may also be used to represent fields of a subclass when performing object/relational mapping.
• A performance denormalization, when done as part of physical design. If the additional attributes are rarely needed, they can be shunted off into a separate table that can be joined in if needed. However, this technique is likely not needed if your database can optimize using a covering index or a materialized view to create a physical representation of the frequently-accessed subset of the data.