Database Design – External ID for Every Relational Table

databasedatabase-designrelational-database

Should I add an external id for every table?

For example if I have these tables :

Customer
=======================================
Id       Name      Gender
---------------------------------------
1        John      M
2        Doe       M
---------------------------------------

CustomerPurchase
==========================================================================
Id       ExternalId      CustomerId            TotalQty
--------------------------------------------------------------------------
1        PO0313-0001     1                     10
--------------------------------------------------------------------------

In that example, I understand that ExternalId is required in CustomerPurchase table (for printing, etc).

But I don't see any reason why Customer table should need one as well.

FYI, the reason I asked this because a friend of mine said that SAP uses ExternalIds in their tables.

Should I use ExternalId in every table?

Am I missing any ExternalId's other usage besides for user's eye friendlier identification?

Thanks!

Best Answer

In layman's words:

Use surrogate primary keys as primary keys when:

There are no natural or business keys
Natural or business keys are bad ( change often )
The value of natural or business key is not known at the time of inserting record
Multicolumn natural keys ( usually several FK ) exceed three columns, which makes joins too verbose.

If a natural key exists that doesn't fall into the conditions listed above, for the sake of God, use it, especially if the key is an ISO standard or is issued by some respected institution, like country codes, airport or airline IATA codes, MAC addresses, car plaque numbers, IMDB movie codes, radio station call signs, etc. That would allow you to interoperate easier.

Above conditions mean surrogate keys will have to be used in many tables. But not all.

Related Solutions

Logging – Log to File or Database Table: Which is Better?

Since you are using this information for billing purposes, I do not see why you would not want it in the database where it can be easily queried, aggregated, reported on, and joined to other data.

I also think it's much easier to maintain a single database table containing the log information than a bunch of separate log files. Same with your concern about the load on the server - there are much better ways of dealing with that than resorting to keeping data in flat files.

Your third option, by the way, is to do both. Use the database for most needs, but have the log file for auditing purposes.

Database Design – Relational Table Design for Data Ownership

Your request table looks fine, keep it as it is, just add a general concept of "keeping track of historical records" to your other tables, this will solve all your three additional requirements.

Start by implementing the concept of ownership in a separate table "ownership":

+---------------+--------------+------+-----+
| Field         | Type         | Null | Key |
+---------------+--------------+------+-----+
| id            | int(11)      | NO   | PRI |
| resource_id   | int(11)      | NO   |     |
| owner_id      | int(11)      | NO   |     |
+---------------+--------------+------+-----+

(and remove the owner_id from resource).

Now, extend this model by adding a "history functionality" to the ownership table. There are basically two standard ways to accomplish this: either you add a nullable timestamp field to this table and use the convention "timestamp=null" means "currently valid", "timestamp set" means "ownership in the past". Or, you create a shadow table "ownership_archive", with exactly the same attributes as the "ownership" table plus the timestamp field. Then you can move old ownership records which are not valid any more after approval of a request to that table.

If necessary, you can implement similar "history functionality" for your "resource" table and your "users" table, but that will be only necessary if you need to track the history of resources and users in detail as well.

You can find a broader discussion about storing historical data in a database in this older stackoverflow question.

Best Answer

Related Solutions

Logging – Log to File or Database Table: Which is Better?

Database Design – Relational Table Design for Data Ownership

Related Topic