As we want to provide a web service with the IBM champions of 2021 we need to store the data at some place … and what better place than in a SQL table.

What infos do we want to provide:

• the country the champion lives in
• the name of the champion
• the awards of the champion (he may have more than one)

There are probably some ways to lay this out in tables. One way would be the following:

Ids

We use integer ids for everything (except the country where we use the country ISO 3166-1 alpha 2 code). Those ids will be automatically generated as we are not really interested in the id. The id is just a number used in the persistence process. It holds no real value and is not interesting to the user. Ideally the user will never see this id.

Note: If an id is important to the user and he will actually “work” with it it should really just be another data attribute and the data model should also get another id for persistence. In this case I like to differentiate between an id which is just used for persistence (pid or persistence id) and an id which represents the data to the user (bid or business id).

Versioning

We want to make sure that the users don’t override the same record without noticing that it had already been changed by another user. In the 5250 world this is easily done by locking the record on reading it (pessimistic locking). As we are developing (stateless) web services this concept won’t work for us (and does not scale very well in the web world).

What has evolved as a standard in locking strategies is the opposite of pessimistic locking … optimistic locking. When using optimistic locking there is nothing actually locked. A version attribute is added to the data model which is passed to the client and sent back to the server. If the attribute on the server is newer (timestamp) or higher (integer) than the one sent by the client some other client has already changed the data on the server and the client gets informed that it sent stale data.

So we add a version attribute to the champion table as this is the entity that the user will be able to change.

Referential Integrity

There are some people who say:

Put everything in the database!

What they mean is to put every rule into the database definition. I am not a big fan of this everything thing. Not every rule can be well expressed with SQL and if the SQL statement is not easily readable then it is also not easy to maintain which makes it “legacy” before it even gets to production.

But what I want to put in the database definition are rules that keep the integrity of the data safe and complete. So in our case there must be a country data record for every country key in the champions table and so on.