1

I've read several posts on SO about creating one-to-one relationship:

how do i create a real one-to-one relationship in sql server

is there ever a time where using a database 11 relationship makes sense?

Database design 1 to 1 relationship

but I would be pleased to have your advice on this basic case: I have a USER and a COMPANY tables. Each USER can have 0 or 1 COMPANY.

Is it better to use two tables with the following relationship:

USER_COMPANY

RELATIONSHIP

or did I have just to use only one table with all fields needed:

USER

Thanks for your explanation.

Note: I'm using SQL Server (with Manager Studio to set it up) and plan to use EF.

Update: In order to be more explicit, what I'm trying to achieve is the following: A user can own or doesn't own a company. If he owns a company he is the sole person working for it.

Thanks

Community
  • 1
  • 1
benoitr
  • 6,025
  • 7
  • 42
  • 67
  • A couple of questions here. Can a user change companys with time?, if it can, is it important for you to know the company for wich that user worked?. Can a company change his name?. In any case, your first design doesn't really make sense, since there is no `CO_ID` on the table `USER` for you to create a relationship – Lamak Aug 23 '12 at 20:40

5 Answers5

3

Since it is possible for a user not to own a company, this is not a true "1 to 1" relationship.

In fact, this is "1 to 0..1", and you can model it in one of the two ways:

  1. Have everything in one table:

    enter image description here

    Note how COMPANY_ID is both UNIQUE (preventing multiple users from owning the same company) and NULL-able (allowing for the users that don't own a company). The separation of USER_ID and COMPANY_ID is what allows company-level foreign keys (i.e. allows child tables to reference company, while preventing them from referencing company-less users).

    If there are no company-level FKs, you can omit the COMPANY_ID altogether.

    You'll also need a CHECK to ensure no other company field can be set unless COMPANY_ID is set (or at the very least that the correct subset of company fields is non-NULL).

  2. Have two tables:

    enter image description here

    We can't just have PKs of these two tables also be FKs (in both directions) because MS SQL Server does't support deferred constraints that would be needed to resolve the chicken-and-egg problem when inserting new data, nor it would correctly model the "1 to 0..1" relationship (it would model "1 to 1" and not allow company-less users).

Which one of these two strategies should you choose depends largely on the number of companies compared to users:

  • If most users own a company, choose (1).
  • If there are many more users than companies, choose (2).
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
1

This actually sounds like a many-to-many relationship to me:

  • Multiple users can work for a company
  • A user can work for multiple companies

Perhaps you don't want to account for the latter case, but i can't hurt. In which case you'd have a user table, a user_company table (n-to-n mapping), and a company table. If you want to restrict a user to one company, make the user_id column in the user_company table unique.

Hopefully that helps.

Madbreaks
  • 19,094
  • 7
  • 58
  • 72
0

It depends on what other data you are going to have. If this is the only place company is used, then go for it in one table. If company is used in several other tables, then you would want have it be a separate table.

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
0

Your first option is best.

  • Indexing...performance...
  • If users are not companies...
  • If you would ever want to query users without bringing back companies...
  • ...or vice versa
  • If you would ever have other tables that needed to link to users, but not companies...
  • If you would ever want to be able to secure the tables differently (maybe your purchasing department needs SELECT access to companies, but not users; your hr department needs access to users but not companies)...

There's a million reasons...but here are 5 or 6 to think about / get you started.

Chains
  • 12,541
  • 8
  • 45
  • 62
0

Hmmm If you have no likely requirement to ever have more than one company per user,and the bulk of users will have a company and it's not that many fields, to describe a company, then I'd keep it simple and go with one table. You can always chnage your mind later on.

If you want a bit more scope 1 to many, then you put userid in company or companyid in user, what you have as option 1 isn't going to work and would lead to incredibly confusing code as well. 

Select Company From Companies Where CompanyID = @UserID;// huh eh? What!!

And as others have said if you want even more scope so you can do many to many, then a 3rd table with CompanyID and UserID in it would be required.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39