Alternative for composite key

Question

hi,

i have database structure where all the tables are having two columns for primary key.

for an example the table Author has two columns like AutherId which is a auto increment number and the pc_id which is unique to that pc the those are the composite keys for the table. but when it comes to relations i have to define the both columns for each and every relation. and since im planning to use Docrtine (PHP ORM) it is bit problematic to use it like that.

so i wonder if i could generate a unique id (combining the pc_id as well) and use it as the primary key.

the php code is like time() . rand(1000,9999) . $pc_id

so that the id is generated by concatenating time + a random number between 1000 and 9999 and the pc_id (pc_id is also a number starting from 1). but this makes a 20 digit number (when the pc_id is 6 digit) which needs bigint to store

is there a good alternative for this

Regards

Answer 1

The author table only needs one primary key - AuthorId. Why would you ever have two authors with the same id? If you have a many pc to author relationship then have a separate pc table with a primary key of PcId, and a field for AuthorId.

Answer 2

It is often considered to be a good idea to have the DB to auto generate a primary key in stead of using business information for this purpose.

Of course you can still have contraints on your business information if you want to enforce uniqueness. But this way you can avoid the problems that you have with foreign keys and such.

So i say, generate a primary key, and get a unique constraint on your business values

Answer 3

You could use a UUID as the surrogate key. It's globally unique across all servers. The downside is that it's a 36 byte string, which can be unwieldy to pass around manually.

If you can meet the conditions, there's also uuid_short(), which is a 64bit integer representation of the UUID, but it's got specific restrictions on how/when it can be considered globally unique, as it drops half of the string UUID's 128bits so it can squeeze into an "bigint unsigned" field.