Search code examples
databaselinuxpostgresqlsolr

Any reason to have a numerical ID in a database?

I basically have a lot of different databases which get combined into one SOLR store. It has to be this way, and I cannot change it.

I was giving each item in each database a unique ID but when I combined the databases into SOLR I found that I was having more than one item with the same ID, for example:

Database A
Itemid: 1

Database B
Itemid: 1

SOLR:
Itemid: 1
Itemid: 1

Instead I am now having my main ID with the database name before it, like this:

Database A
Itemid: A1

Database B
Itemid: B1

SOLR:
Itemid: A1
Itemid: B1

My question is, is there any valid reason for having a non numerical ID, can I still do sorting based on these IDs?

Solution

  • If you care about performance than it's better to stick with numeric IDs. (See Database Design Primay Key, ID vs String)

    But to accomplish your task I would add the database name prefixes to Solr IDs at the data importing stage. So you would have:

    Database A
Itemid: 1

Database B
Itemid: 1

SOLR:
Itemid: A1
Itemid: B1