27

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.

Community
  • 1
  • 1
user118190
  • 2,139
  • 7
  • 29
  • 45
  • 2
    Just keep in mind there going to be a trade off between database appearance (with nice split up tables) and query complexity (when you suddenly have to join all these tables) – Tim Oct 15 '10 at 05:07
  • Will all items have all attributes? – Dean J Oct 15 '10 at 05:07
  • 1
    Another point is, depending on your ORM - you can map the single table to multiple entities. You can do this with .NET Entity Framwork 4. The end result - better database design AND better OO in code. – RPM1984 Oct 15 '10 at 05:13
  • 1
    Dupe: http://stackoverflow.com/questions/517417/is-there-ever-a-time-where-using-a-database-11-relationship-makes-sense – Aiden Bell Oct 15 '10 at 05:14

5 Answers5

52

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    Thanks! My thinking is more in line with yours! Greatly appreciate your response! – user118190 Oct 15 '10 at 05:22
  • 2
    I know this is an old question, but I have many examples of legitimate 1:1 relationships, for most of which they are the ONLY correct solution: http://stackoverflow.com/questions/517417/is-there-ever-a-time-where-using-a-database-11-relationship-makes-sense/28313151#28313151 – Tripartio Jan 18 '16 at 19:44
7

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.
Jeffrey Hantin
  • 35,734
  • 7
  • 75
  • 94
5

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.

Beth Lang
  • 1,889
  • 17
  • 38
5

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.

Aiden Bell
  • 28,212
  • 4
  • 75
  • 119
  • **Definitely** avoid EAV's - see here why: http://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/ – marc_s Oct 15 '10 at 05:07
  • Tell that to Magento ;) tho I agree with you – Aiden Bell Oct 15 '10 at 05:08
  • 3
    In my experience, the only excuse for EAV is when you need a runtime-modifiable schema *and* have a paranoid or obstructionist DBA that doesn't like anything but an install script issuing DDL. And if you're actually considering EAV, why not toss SQL overboard and use a key-value store or something? – Jeffrey Hantin Oct 15 '10 at 05:15
  • 2
    @Jeffrey - couldn't agree more, was just making a point about the road you may wander down with 1:1-ing – Aiden Bell Oct 15 '10 at 05:16
  • Heh. I've developed a tendency to really get fired up about anti-patterns ;-) – Jeffrey Hantin Oct 15 '10 at 05:19
  • Very good information and I've reread all of great answers! Thank you so much! – user118190 Oct 15 '10 at 05:21
1

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.

Dean J
  • 39,360
  • 16
  • 67
  • 93