Foreign key?

Databases
1

I want to know if im setting up the relationship right on these 2 tables, providers, and territories. Since 1 provider can have many territories…
Heres the provider table

CREATE TABLE `providers`(
`id` SMALLINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(`id`),
....
`created` DATE,
`display` CHAR(1),
UNIQUE KEY(`email`)
);

the territories table

CREATE TABLE `territories` (
`id` TINYINT UNSIGNED AUTO_INCREMENT,
`p_id` SMALLINT UNSIGNED,
`state` CHAR(2),
`city` VARCHAR(50),
FOREIGN KEY (`p_id`) REFERENCES `providers`(`id`),
PRIMARY KEY (`id`)
);

Did I set this up right?

2

[quote=“lukeurtnowski, post:1, topic:197440, full:true”]Did I set this up right?
[/quote]
if each territory can be assigned to only one provider, yes

3

My logic must be screwy then cause Im trying to make it so that 1 territory can have many providers though

4

Your original post needed to say both these things. :wink: Right now you’ve achieved the first, which r937 pointed out, but you haven’t yet achieved the second. You’ll need to learn about many-to-many relationships and junction tables.

1 Like
5

is this ok

CREATE TABLE `providers`(
`id` SMALLINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(`id`),
`name` VARCHAR(50),
`email` VARCHAR(50),
`phone` VARCHAR(20),
...
);

CREATE TABLE territories (
`id` SMALLINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(`id`),
state CHAR(2),
city VARCHAR(50)
);

CREATE TABLE provider_territory (
provider_id SMALLINT REFERENCES id (providers),
territory_id SMALLINT REFERENCES id (territories),
PRIMARY KEY (provider_id, territory_id)
);

Is that junction table correct?

6

[quote=“lukeurtnowski, post:5, topic:197440, full:true”]Is that junction table correct?
[/quote]sure is!

although if it’s me doing this, i would not have an auto_increment in the territories table, but use city and state as a compound PK, and therefore as a compound FK in the junction table

7

I, on the other hand, would keep auto_increment and avoid natural keys. :wink:

I think the real world is rarely as perfect as our technology requirements need it to be. For example, I did a quick search and found that city/state combinations might not be as unique as we’d like. And even social security numbers, often cited as the perfect example of a good natural key, might not be unique due to fraud. Plus, business rules change. What if down the road you decide you only want zip codes rather than city/states? Or what if for security reasons you decide to stop storing social security numbers?

I think natural keys increase the risk of obscure bugs for very little benefit.

8

oh.
So if a state has 2 cities of the same name, then the compound PK wouldnt work?

9

I know of no (US) state that has two cities with the same name.
There are some like “East City”, “South City”, or “Berg”, “Saint Berg”, “Town”, “New Town” etc.
As long as you consider that I think you’ll be fine.

If you don’t mean United States,check.
Just because the US doesn’t go with County boundary limits doesn’t mean neither does elsewhere

10

I like the option where rather than checking and thinking we’ll be fine, that instead we don’t check and know we’ll be fine (cough cough surrogate keys :wink: ).

11

I’ve been thinking.
It could lead to a rare Bug that would be very difficult to pin down, if it wasn’t documented

Though I’m 99.9% confident that it would be fine for the US, there’s is a nagging “small voice” that says “but …”

So if one were to use id alone, how would you prevent things like “St. Paul”, “Saint Paul” from happening?

12

There’s a way you can be 100%, and it takes less effort. :wink:

But seriously, if we’re going to sacrifice robustness, then natural keys had better offer very significant advantages.

What makes you think natural keys would prevent that? They’re different strings, so they would be considered different keys. Regardless of whether we were using surrogate or natural keys, we’d probably need access to a national postal database to look up a canonical address.

13

[quote=“Mittineague, post:11, topic:197440, full:true”]So if one were to use id alone, how would you prevent things like “St. Paul”, “Saint Paul” from happening?
[/quote]the id wouldn’t prevent that

if you’re using a surrogate key, though, you would ~definitely~ also want a UNIQUE constraint on the natural key, in this case city + state

so the UNIQUE constraint prevents “Saint Paul, MN” from being entered twice

so right there, that puts paid to the notion that “natural keys increase the risk of obscure bugs for very little benefit” which is somebody’s opinion, but only an opinion

however, neither the id with UNIQUE constraint on the natural key, nor the natural PK, will prevent “Saint Paul, MN” and “St Paul, MN” from being entered

and think about this – under what circumstances would you need to change the natural PK?

well, there can be only two possibilities –

  1. a city changes its name
  2. a city moves to another state

all you surrogate key fans can now explain how the surrogate key makes either of the above scenarios easier to deal with… take as long as you like

me, i prefer the natural FK in this scenario – nobody should try to expand this discussion to social security numbers – each case should be dealt with on its own merits

in this scenario, any time you need to search the junction table (e.g. find all territories for a particluar consultant), you need one less join – there’s a solid advantage right there

14

The whole point is we can’t truly be sure if a city + state combination is unique in the real world. I spent just one minute on google and already found some likely exceptions to that assumption. So no, we don’t definitely want a unique constraint.

EDIT: Let’s say, for example, that we use city + state as a natural key, which means we’ve also used city + state as foreign keys. Then the day comes when we realize our assumption wasn’t as solid as we thought. We realize that city + state wasn’t good enough to uniquely identify a location, and we decide we need to add a zip code.

We shouldn’t needlessly rely on assumptions like that. There’s no benefit to it.

15

Off-Topic perhaps, but I would be interested in those.
I know there is a a Springfield MA and a half dozen or so other Springfields, but I know of no state that has replicate city names.

My “tickle” is that I’m not entirely 100% certain, and if the database were “extended” to work with non-US, I would be much less certain.

So if some type / combination of keys would make things more robust it would be good to know

16

Just had a quick look on wikipedia and it lists 5 towns called Springfield in the state of Wisconsin so if you were two use it as a natural key you’d have to use the city/town name, the county and the state.

Can a place ever get its zip code changed or is a place’s zip code set in stone for all time?

17

Five Wisconsin Springfields ?

For my life limited time frame, probably yes. Else, probably not.

eg. years ago I would have thought my telephone area code was a CONSTANT

Not so. nearly a quarter century later and they differ due to the need to accommodate population growth. .

AFAIK code lifetime is short. So maybe in a lot of cases it’s a matter of “do I care about fifty years from now?”

18

It can change. Already happened to me once.

19

So what would you suggest as the best database aproach to take to allow for the unpredictable unknown?

20

I personally would steer clear of using town/city names, county names or zip codes as any could be changed.

Phone numbers over here have changed a few years ago now, phone codes (area codes) outside of London and possibly a few big cities were 4 characters long, an extra 1 got added.

@r937 from an indexing point of view, do searches based on numeric or text (string) keys search faster, which indexes better?