Natural and surrogate identifiers in database relations

[TopSpek]

gpietersz
It looks to me that your code does not match your requirements because

TopSpek
A particular person cannot be leasing the same app at the same time

gpietersz
Implies you need to deal with multiple apps which your code does not.

My apologies. I did think about including this, but the example was meant to be a demonstration of how natural and surrogate keys are used at the design stage.

However, my example does show that lease periods of the same app, for a particular person, not overlapping, does address your initial misunderstanding.

I was trying to keep things as simple as possible. If you would like me to show you how an entity type 'App' would be added to the database schema, then I can do this. But for now, please find below some sample Lease tuples, with a foreign key to reference apps. (Please note that there would be a 0:m relationship from App to Lease if we developed the database to include an App entity type):

Person( PersonId )
Lease( StartDatetime, EndDatetime, PersonId, AppId )
App( AppId )

Lease
--------------+-------------+----------+------
StartDatetime | EndDatetime | PersonId | AppId
--------------+-------------+----------+------
01-03-2020  | 01-04-2020  | p1  | a1
10-03-2020  | 10-04-2020  | p1  | a2
01-04-2020  | 01-05-2020  | p1  | a1
--------------+-------------+----------+------

(In the datetime stamps, for clarity, I have omitted the 'time' component and reversed the format of the date component. You will need to line up the pipe characters because the codebox won't do it.)

We can see that a1 is leased by p1 twice but the lease time periods do not intersect. p1 also leases a2, and this time period intersects with both of p1's lease periods for a2 - which is legal because a1 and a2 are different apps.

By the way, I'm not having a go at you, but you would learn more if you did some of the work for yourself instead of me having to show you everything. I don't mind spending time with anyone who wants to learn, but a developer would at least have a crack at some of these things. If we make mistakes, then that's fine - we all learn by our mistakes.

gpietersz
It rather proves my point about how easy it is to make mistakes!

Think of the requirements I gave as being from a fictitious client. The requirement that "a particular person cannot be leasing the same app at the same time" makes perfect sense. If this constraint were not implemented, then the person could be paying for the same lease, fully or partially, twice! In other words, if a person is leasing an app, then that person should be protected from taking out another lease on the same app while the current lease is still active.

Furthermore, the constraint dictates that the lease has the natural identifier (PersonId, StartDatetime) which should be preserved in the schema. This is fundamental and axiomatic relational database theory which has been developed over decades and is now well established - like many other areas of maths.

 

[gpietersz]

Its not relational database theory that is the problem.

It is making assumptions about how requirements may change.

but you would learn more if you did some of the work for yourself instead of me having to show you everything

I cannot recall asking for tuition. You have repeatedly assumed that I (and Alan) are in need of enightenment from you and assumed that you have nothing to learn here. Wrong.

 

[TopSpek]

gpietersz
What if business requirements change and you later need multiple people per lease (as Allan suggests).

As I've already said, that's fine if you've discussed your idea with the client and they've approved it. Remember that it is his/her business model, not yours. You should not take it upon yourself to tell your clients what's best for them.

Let me repeat: the 'extension', which has not been requested, significantly changes what the client asked for. The client asked for one person to be on the lease, but you have decided that many people can be on the lease. A good developer would be careful not to veer away from the client's requirements, but at the same time would of course notify the client of any potential improvements which could be made.

gpietersz
I am reluctant to denormalise - but sometimes you have to.

You can't "denormalise" until you have first normalised! And you have strongly suggested several times in this discussion that you don't agree with database normalisation. However, I think you would feel differently about this if you actually understood what database normalisation is.

Again, I am not having a go at you, but sometimes you often sound as though you're picking up these little remarks from other articles and discussions on the web, and you think we can't tell!

Lastly, I haven't mentioned normalisation for my sample exercise above because normalisation often requires complex mathematical and programmatic tests, and I didn't want to complicate things unnecessarily in a simple example. But in my software, I aim for third normal form (3NF) in order to avoid the vast majority of insert-update-delete anomalies whilst maintaining optimum overall efficiency, amongst other good engineering and programming practices.

If you need further help, then as ever, I am willing to help you, but I will be a little busy over the next few weeks so please excuse me if my answers are a little delayed.

 

[gpietersz]

You should not take it upon yourself to tell your clients what's best for them.

Oh yes you should. You are the technical expert and it is part of your job to understand requirements, not just blindly implement them.

Of course, who "you" are depends on project size. If you are a single person doing all the work (e.g. a freelancer working for SMEs) its you. In a bigger team it may be that a different person does this (although with feedback from developers). I have been in both these positions.

You can't "denormalise" until you have first normalised! And you have strongly suggested several times in this discussion that you don't agree with database normalisation.

No, I have argued in favour of an implementation detail.

I think the problem here is that you have been taught by people who are excessively purist or were keeping things simple for an undergraduate level course. On top of that you are obviously too inexperienced to have realised the real world problems this creates.

I suggest you read things like the introduction to this paper: https://link.springer.com/chapter/10.1007/978-3-642-15576-5_41

If you want more resources and real world examples I am happy to provide them, but you need to be willing to learn or its a waste of my time.

 

[Alan]

This is a funny thread.

You should not take it upon yourself to tell your clients what's best for them.

Depends on the job - most of us here are not developers in a big team ( any more ) and work at the expert level ( consultants, analysts, solution architects, designers, project managers ) and our day jobs are absolutely telling ( advising ) the client what is best for them. That is the difference between £25/hour and £150/hour.

 

[Alan]

Lastly, I haven't mentioned normalisation for my sample exercise above because normalisation often requires complex mathematical and programmatic tests, and I didn't want to complicate things unnecessarily in a simple example.

I think you are over complicating 3NF - now admittedly when I learned 3NF in the real world from the founders or Oracle UK a long long long time ago, there were no university courses, but it was described as a simple concept. For people who have been deigning databases, 3NF is the starting point and almost a natural view of things of the theoretical design. Only once you have the 3NF down then it is a case of implementation design which may include denormalisation for practical reasons ( performance mainly ) and this is where most RDBMS have triggers and transactions so that data integrity can be maintained in the denormalized design.

 

[TopSpek]

gpietersz
I suggest you read things like the introduction to this paper: https://link.springer.com/chapter/10.1007/978-3-642-15576-5_41

That, dear boy, is not a "paper" - it's a book with another daft article pinned to it, or, in other words - a rip-off!

I only needed to read the first three sentences of that ridiculous Abstract to know what an amateur book it is.

From gpietersz' book:

"The natural and surrogate key architectures are two competing approaches to specifying keys in relational databases."

What a pile of utter claptrap:

(1) Natural and surrogate keys are not "architectures".

(2) Natural and surrogate keys are not "competing approaches".

(3) Natural identifiers are not specified in relational databases - they are identified at the database design stage and written into the schema, not the implementation.

If this was a scientific "paper", then they would understand these concepts, and they would be much more accurate with their terminology.

They also claim:

"Our results provide new insights ..."

LOL!

So, after you read their book gpietersz, which "new insights" did you find particularly interesting?

 

[TopSpek]

Alan
... our day jobs are absolutely telling ( advising ) the client what is best for them.

Surely you have to agree that "telling" and "advising" are somewhat different.

I don't know whether you've read the relevant messages in this discussion, but we are discussing whether it is acceptable to ignore the client's requirements, and return to them with something completely different to what they asked for, without first discussing it with the client.

Is this really what you do with your clients?

 

[TopSpek]

Alan
...now admittedly when I learned 3NF in the real world from the founders of Oracle UK a long long long time ago, ...

And how do you think the likes of Oracle started and developed? Do you think Oracle invented relational database theory, normalisation, third normal form, etc.?

Inventions and discoveries like these came from the universities in the unreal world. And to be sure, the vast majority of inventions and discoveries which came from your real world, actually originated from people who had been through the unreal world of academia.

... there were no university courses,

Good Lord! How many centuries have you been alive for?!

And after all this time - you're still on only £150 per hour!

 

[Alan]

You are very witty.

I think relational database theory was developed from set theory, but by IBM.


In terms of rates, they go down at my stage of career, think of it as a bell curve, enjoy it on the way up. Mine peaked about 12 years ago, and I'm well down the other side, but at least that now means I can work on what want to when I want to

 

[gpietersz]

@TopSpek its an extract from a book which was presented at an academic conference and was available separately from the book. It is listed as a paper here: https://link.springer.com/book/10.1007/978-3-642-15576-5

If you had correctly read the details that would have been obvious.

So you are now in the position of saying that not only are @Alan and I talking nonsense, but so is a professor of computer science and his co authors

 

[TopSpek]

@TopSpek its an extract from a book which was presented at an academic conference and was available separately from the book. It is listed as a paper here: https://link.springer.com/book/10.1007/978-3-642-15576-5

I don't care where it was listed - I know for a fact that's it's garbage.

Where are the peer reviews?

Where are these "new insights"?

Please find all of these things; study them; critically analyse; support/correct/reject as required; then come back to me with your own peer review!

The truth is, you don't understand a word of it because you haven't even read it!

Bizarre how you can have an opinion of something which you haven't even studied!

 

[TopSpek]

Alan
I think relational database theory was developed from set theory, but by IBM.

Close, but I think you'll find that Edgar Codd did that whilst working at IBM. Edgar Codd was a devout academic who played a crucial role in IBM's progress.

Sounds similar to something I said a couple of posts ago!

 

[gpietersz]

And after all this time - you're still on only £150 per hour!

That is a bit of a cheek coming from someone whose rate on his PPH profile is all of £30/hour! Its not relevant either.

 

[AllUpHere]

That is a bit of a cheek coming from someone whose rate on his PPH profile is all of £30/hour! Its not relevant either.

Well played; there's no coming back from that.
We have a winner

 

[TopSpek]

£30 per hour is indeed my advertised hourly rate.

Now, I'll give you one guess as to whether I earn more than £30 per hour, or less than £30 per hour, via PPH.

Come on - take a shot!

I will reveal the answer when I receive your responses.

Also, why do you think that Alan did not take offence at my remark? Could it be because he was clever enough to get the joke?

 

[UKSBD]

Thread temporarily locked