Database Design - Should one-to-one relationships be avoided? [duplicate]

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

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 2

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.