PK for table that have not unique data

Question

I have 2 tables like

Company( #id_company, ... )

addresses( address, *id_company*, *id_city* )

cities( #id_city, name_city, *id_county* )

countries( #id_country, name_country )

What i want is :

It is a good design ? ( a company can have many addresses )

And the important thing is that you my notice that i didn't add a PK for addresses table because every address of a companies will be different, so am I right ?

And i will never have a where in a select that specify a address.

Answer 1

An address is a thing and should have its own table.

An address can exist without a company, therefore it should not have a foreign key to company. Also, what if you start selling to/buying from individuals?

A company can have zero, one, or many addresses.

Two or more companies can have the exact same address. You assumption is flawed.

Use a junction table:

company -< company_address >- address

Answer 2

I'm making this an answer instead of a comment because of length. As to the address table having a defined primary key, the answer is yes. There are several good reasons but just consider this one.

Suppose a company had several addresses and a move required you to delete one of the addresses. You can't just delete where comp_id = x as that would delete all the addresses for that company. You have to have where comp_id = x and something_else where the something else must differentiate the one address from all the others for that company. So you have to have someone look at the different addresses to see how they differ and select the one difference that correctly identifies the one address and then write that correctly into the where clause.

That's a lot of work to do every time you want to delete (or update) an address.

It also means it's more difficult to write a parameterized delete statement that can be used to delete any address. Suppose a company has several locations in the same building: Shipping in Suite 101, Marketing in Suite 202 and IT in (of course) the basement. So the street, city, state, everything is the same, different only in Suite_No or whatever is used to refine the address.

Then consider your user. Most of the time, a user isn't going to be interested in seeing every single address you have listed for a company. He's only interested in Product Testing. You should be able to give them Product Testing's address and no other. Users are not known for their patience when presented with a data dump every time they do a query and it's up to them to select the one they're looking for.

It just solves so many problems to be able to specify where addr_id = x.

Answer 3

First of all we should distinguish natural keys and technical keys. As to natural keys:

1. A country is uniquely identified by its name.
2. A city can be uniquely identified by its country and a unique name. For instance there are two Frankfurt in Germany. To make sure what we are talking about we either use the distinct names Frankfurt/Main and Frankfurt/Oder or use the city name with its zip codes range.
3. A company gets identified by its full name usually. Or use some tax id, code, whatever.
4. To uniquely identify a company address we would take the company plus country, city and address in the city (street name and number usually).

You've decided to use technical keys. That's okay. But you should still make sure that names are unique. You don't want France and France in your table, it must be there just once. You don't want Frankfurt and Frankfurt without any distinction in your city table for Germany either. And you don't want to have the same address twice entered for one company.

• company( #id_company, name_company, ... ) plus a unique constraint on name_country or whatever makes a company unique
• countries( #id_country, name_country ) plus a unique constraint on name_country
• cities( #id_city, name_city, id_county ) plus a unique constraint on name_city, id_country
• addresses( address, id_company, id_city ) with a unique constraint on all three columns

From what you say, it looks like you want the addresses only for lookup. You don't want to use them in any other table, not now and not in the future. Well, then you are done. As you need a unique constraint on all three columns, you could just as well declare this as your primary key, but you don't have to.

Keep in mind, that to reference a company address in any other future table, you would have to store address + id_company + id_city in that table. At that point you would certainly like to have an address id instead. But you can add that when needed. For now you can do without.

Answer 4

This design is fine.

A (relational) table always has a (candidate) key. (One of which you can choose as the primary key, but candidate keys, aka keys, are what matter.) Because if no subset of columns smaller than set of all columns is unique then the key is the set of all columns.

Since every table has one, in SQL you should declare it. Eg in SQL if you want to declare a FOREIGN KEY constraint to the key of this table then you have to declare that column set a key via PRIMARY KEY, KEY or UNIQUE. Also, telling the DBMS what you know helps optimize your use of it.

What matters to determining keys are subsets of columns that are unique that don't have smaller subsets that are unique. Those are the keys.

A company, address or city is not unique since you are going to have multiple of each.
A (city,address) is not unique normally.
A (city,company) is not unique normally.
A (company,address) is not unique normally.
So (company,address,city) is the (only) (candidate) key.

Note that if there were only ever one city, then (company,address) would be the key. And if there were only ever one company, then (address,city) would be the key. So your given reason that the "because every address[+city?] of a company [?] will be different" isn't sound unless we're supposed to assume other things.

Answer 5

It's okay - you might want to add some non-unique index on company_id so company address queries are sped up. Another option would be making a joining table between Company and Address, but that would probably only be justified if Address stored more data(so searches would be slower).