3

I have an "easy" question, but since I am not a very experienced programmer I would like to hear what some of you have to say.

If I have for example a Db called MyDb with many tables. Two of them are Company and Address.

The relation between them is that a company can have a lot of addresses (each for one office or sth)

So now I was thinking about it and was wondering how a "default" address should be implemented.

I imagine 2 different approaches:

  1. Create fields CompanyId and Default(boolean/bit) in Address table. If Default is true, then this entry is the default address.
  2. Create fields CompanyId in Address and AddressId in Company, which represents the actual default address.

This is not my specific problem, it's a generic blueprint, so to say.

So, now which is the better approach? 2 appears to me reaaaaally uggly. With 1 how could I avoid having 2 defaults as true? I mean not in code, just leaving the DB do this (like it says sth. when you enter the wrong type in a field?

5

There is usually a joiner table between Company and Address in a many to many relationship. Let's call it Company_Address.

Company_Address would have Company_Id and Address_Id with relations to Company and Address. It would also have an additional column; the Default Indicator.

The Company and Address tables would not have any default indicator. The joiner table can also have other fields as well if needed. If you change default address for a company one would update the joiner table record(s) as needed.

3
  • That's a nice approach! Thank you, will try it this way. Could then i use the same approach to relate dataFiles to Company or Address? How should I store the files (files are typical pdf, excel, word, etc..)?
    – R. Gomez
    Nov 29 '17 at 21:57
  • 1
    So more than one company could have the same address?
    – JeffO
    Nov 29 '17 at 22:56
  • No, a company can have a lot of adresses (offices, stores, whatever) but ONLY ONE DefaultAdress. An address can ONLY belong to ONE company.
    – R. Gomez
    Nov 30 '17 at 15:10
2

Your first example is perfectly reasonable. Just have default field in the address table. This is a one-to-many relationship. And the "type of address" is an attribute the address table.

If you really want to a design schema to prevent multiple default addresses for one company create a third table called CompanyAddressDefault with two fields companyid and addressid. Then make companyid the primary key.

1

You really have two relationships here. Company -> Address is 1:n, but Company -> Default Address is 1:1.

The canonical way to model a 1:n relationship is to put a foreign key on the n side pointing back to the 1 side. In other words, put a Company id column in your Address table. You are already doing this.

The canonical way to model a 1:1 relationship is with a foreign key on one of the 1 sides. Since Company is the only 1 in all relationships between Address and Company, you would put an Default Address id column in your Company table.

Note that using a "joiner" table doesn't allow the schema to enforce that there must be a default address. If that's okay, and you find it cleaner, then so be it. However, I think the "ugliness" you mentioned in solution 2 was due to the fact that you had to update two tables when creating a new default address. Note that you also have to update two tables with a joiner table solution. If you have to update two tables anyway, I would prefer to go with the solution with stronger constraints and fewer overall tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.