Fdtxjr

While creating the architecture of a new project I struggle to come up with a good solution to handle public IDs.
For example our users will receive a membership card. Each card has the membership ID printed on it. But because in our branch of industry we expect cooperation with other businesses the format of those membership IDs may change in future, or we will use additional IDs.

Surely the membership ID is not the internal database primary key or something like this.

These are the two options I came up:

• The membership ID is a property of the user model.
  
  
  • It is easy to query.
  
  
  • But then we have to change the model and the database schema each time we have a new format.
  
  


• The membership ID has its own model which references the user model
  
  
  • This is easy to extend.
  
  
  • But then we have to join two tables for (more or less) each query
  
  

I know it depends strongly on many factors for which this question is to short to mention all, but is it bad practice to expect database schema changes instead of explicitly modelling this kind of flexibility. I mean this will only happen once in few years.

Or do you have some other ideas?

You cannot look into the future - accept that. So better go ahead with a simple, but sufficient solution for today, and add more complexity when you know the requirements, not beforehand.

If you invest too much design efforts into things which you will expect to happen "once in few years", and you don't know yet how the precise requirements will look then, there is a high risk of overdesign those things. Worse, there is a high risk of designing something which will make adding those unknown future requirements actually harder, because they don't look like you envisioned them.

So if you can design the membership ID as an attribute of the user, as a free form string, and this is sufficient for today, then go ahead. If, in a few years you really "need to change the model and the database schema" - then Alas, do it, that will not be the end of the world, it may require some effort, but it is quite unlikely that have to throw away your system and start from zero because of this.

Maybe it turns out even if the format changes, the user attribute in your database scheme is still sufficient, and you only need to change some validation rules in the application. Maybe it turns out there is second membership ID necessary. Maybe it turns out the membership ID needs a time stamp, or it has to be split-up into a combined ID, or it has to match data from an external system, or the ID generation has to become decentralized, or it will not be required any more at all, who knows? You cannot design a system for all thinkable "maybes", so do yourself a favor and don't try it.

You're right; the membership ID is not part of your Primary Key. That's why synthetic keys exist.

A synthetic key is one that is automatically generated, either by you or the database. It usually takes the form of either an incrementing number or a globally-unique identifier. Synthetic keys are never re-used, even if a record is deleted. The good ones are guaranteed to be unique across the entire database, or even across any database.

Synthetic keys don't take on any characteristics of the data they represent. They are, in fact, completely independent of your data, except for the relationships they impart by matching with foreign keys in your other tables.

In short, synthetic keys don't suffer from any of the problems that other kinds of keys do. Each and every one of your tables should contain a synthetic primary key and relationships defined by joining those keys to corresponding foreign keys in other tables.

Naturally, you can add other fields like Membership ID. Avoid using these fields in table relationships, however, except possibly as joins in ad-hoc queries.

Changes to database schemas do happen, but usually only in response to new business requirements. The most common changes made to production databases is that tables or fields are added.

As for good/bad practice in this case, that is mostly subjective, because it depends on factors concerning the application(s) and data.

For example, if you were only to have one business for the initial product launch, and there are no schedules or plans to add other businesses, then there isn't a point to come up with a design that fits accounting for any other businesses.

In trying to plan for multiples, there is the possibility that some additional business does not have the same concept of member as the original business. There are other pitfalls with this route, such as modifying functionality for members based on a request from business "B" then business "A" has member functionality that is not expected or removed. (the first option has more potential to be in the scenario of the example)

For the second option, it could work, but I would still be hesitant, since this is supposed to be a new project. There would be some difficulty in determining shared functionality/properties. Depending on the implementation, there are more opportunities for duplicating code or masking functionality across each member/business.

Let's say that you did have "all" of the requirements for adding new businesses:

Assuming that this project/some other project in your area determines the member id, it does not make sense to have member id be the same internally and externally. If the is any functionality based on the contents of member id, then that functionality has the chance to be broken (EX: not being able to work with historic records, or records that happen to span the dates before and after a change)

Typically, you would want to have some form of Internal Id and External Id. In your case, you could have something like Internal Id: 1234; Business A External Id: M001; Business B External ID : UM001; . Unless you just have a fairly large amount of entries per member or per business, joining multiple tables shouldn't affect performance by much.