CODEWITHSUNDEEP
sqldatabaseschema
Reality Analyst
Reality Analyst

Reputation: 77

Using additional synthetic fields instead of primary keys to reference tables

I've recently had to work with an application where most tables don't reference each other via primary keys, but rather rely on additional synthetic fields. For example, let's say there are tables Person (id, name, token) and Pet (id, name, personToken). One person can have many pets. Pet.personToken would be a foreign key referencing Person.token.

Now, if I was creating the schema, I would simply use Person.id to reference records in that. I was told the application uses "token" columns for the sake of easier data migration between different servers.

My question is, is this something fairly common or is it simply weird design?

Upvotes: 0

Views: 105

Answers (2)

Glenn
Glenn

Reputation: 1167

I can't say if it is common across the industry as a whole, but I have seen the same thing done in a number of applications I've worked on the past.

I think it's a strange design that ends up being put in place because not enough thought has been put into initial designs of the system.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

This seems to be simply a weird design.

I have not seen something like this before.

This seems to be overkill, and I would have to see the Index/Foreign key implementations. Also would not like to see the implementation of token generator...

When you say the I was told the application uses "token" columns for the sake of easier data migration between different servers. is this for replication? Or simply from test to development?

All in all, this does not seem like the best design I have come accross.

Upvotes: 1

Related Questions