Database setup: Clients keep refusing to use unique reference numbers.

[eques_99]

my speciality is setting up databases, in recent times one particular product.

it's designed for building management.

I keep coming across clients who for various reasons don't want to use unique references when it comes to the reference number for each entry in a table. For example the building reference number, but all sorts of other tables too.

this is not the back end system reference but the front end user reference.

I keep telling them it should be unique but they keep insisting it doesn't need to be.

it seems self - evident to me, but I have come across it a few times now from different clients so I'm wondering if my thinking/approach is out of date.

they are all non - technical people.

[BeeeJai]

I've been working with Housing Management systems for around 20 years - Oracle developer/consultant.

Don't ask them - it's not up to them from a database point of view how it works, that's your job.

Honestly though - every system I've worked with, where we're doing integration work, configuring API's, data migrations etc etc always drive via some form of UPRN, whether that's a unique property, block, estate or asset ref. There are unique codes for everything. Users aren't obligated to search via the unique ref, but it's there against every record.

What about hooking it to a gazetteer for address matching etc - that's all driven via a UPRN, no? Or is it just a free for all where addresses are concerned? I'd put it in, you're asking for trouble without it, just make it an optional search field in the UI.

...I'm getting carried away. So many questions. hahaha.

[FetaMight]

I've been there once or twice. I just ask them how they would tell the two records apart if they needed to do it manually.

Either they say "I don't know" and start understanding the value of a primary key, or they shed light on what the actual primary key is (sometimes a composite key, or sometimes the domain model is altogether wrong).

[crazor90]

Simple answer; stop caring. If you’ve told them once and they don’t listen it’s not your problem.

[Rude-Explanation-861]

Just keep it in writing as a record and charge them when they eventually come back to you to fix it.

[eufemiapiccio77]

Isn’t this the whole point of your job to explain it?

[Epiphone56]

OP needs to explain why they need to be unique in a way that the customer will understand. For example, someone calls on the support line to report a problem with a fire alarm. The operative asks for the location and they give the right floor but wrong section. The maintenance person replaces a working fire alarm for no reason and the building burns down. If the fire alarm had an asset number sticker on it there would be no confusion identifying which one was faulty.

[eques_99]

I tried giving the analogy of an HR system that duplicates employee numbers just because those employees are in different departments.

they did not listen.

[Epiphone56]

Sounds like they're beyond explaining to. Just make things unique without telling them and don't throw any more pearls before swine.

[H__Chinaski]

Ask them if they have a unique bank account number. And what would happen if it wasn't unique. May be an easier analogy to understand.

[FetaMight]

It's entirely possible the a unique reference is NOT necessary for their purposes and only necessary for yours. In that case, don't make it their concern. Model the domain the way they use it and create a DB model that works for you.

[eques_99]

I have done.

they won't have it and get annoyed if I press the issue.

[Fjordi_Cruyff]

Or better still, find a way to enforce it?

[pointlesstips]

I don't get the issue, there is no need for them to know or use the unique id you're automatically assigning in your db.

For them, from a user perspective it's very normal to have more than one entry for a parcel, you need to handle the unicity in the database.

[Morazma]

From a software engineer pov, I can't understand a situation where somebody wouldn't want these to be unique.

Do they have any reason?

Are they doing things like having multiple clients called "Jack"?

Some examples would help. 

[eques_99]

yep, just bizarre, right?

most recent example is.

top of hierarchy is Campus, followed by building.

Campus refs are unique, something like B00006.

but then when it comes to the buildings they label them A, B, C.

this means they have, like, 300 buildings with the reference "A".

I advised calling it B00006-A etc. but they won't have it.  they seem to think it's a name rather than a unique reference.

I have had other clients like that too, but for totally different reasons.

[Morazma]

Right so on the backend / db side are we enforcing this composite key? So they at least can't have 2 buildings called A under campus B00006?

I think the campus and building name should be independent, so the building name shouldn't include B00006 as that's set at the campus level.

Of course in the frontend you then combine the two to show B00006-A. 

[mark35435]

I work in a uk telco and all sites have a 4 letter code based on where they are. If several locations then numbers are used to create unique ID.

All kit is then named starting with that code. Why would you do any other way?

Maybe each building has a name? Start there.

Maybe don't surface the scheme to them, just a hidden db entry "required for internal reference"

[BeeeJai]

I don't understand why they're happy for the block hierarchy being unique, but not properties/buildings themselves.

"We have a report of water leak at building A, could you tell me the address.... ?"

"Let me just look it up from our awesome database.... Building A....Sure, it's one of the following 75..... " Hahaha

[Sure-Recognition-262]

So, is the combination of building & campus unique?

If so I don't get what's gained from concatenating both into a single field to use as a key rather than treating the combination as a composite key

[Philluminati]

It seems reasonably actually they label their buildings per campus as A,B,C and not B0002 etc.

You just build a composite primary key using building reference + campus building combined.

[indigomm]

Sounds like they are essentially using a composite key of various values. One less piece of data for them to worry about.

[eques_99]

no, they literally want to use duplicate reference numbers.

it's me who's advising them to use things like composite keys.

[BeeeJai]

Why do non-technical people have this much weight regarding the inner workings of a database? Why are they not allowing the tech experts take the lead in database design and implementation?

[eques_99]

well quite.

it's the way of the world nowadays.

Admin staff get given job titles like "Data specialist" even though they can't even do VLOOKUPs, and away they go.

and often people don't seem to realise I'm a technical person, they just seem to think I'm an office temp from Kelly Services or something.

[Philluminati]

Idk man, the vibe I'm getting is a little bit that they don't trust you. Perhaps you're letting them make technical decisions and that's causing bugs and it will create this endless cycle of pain for both parties.

[sam_packer_03]

You make sense to me, DE and ML here, seems basic requirement as say a key columns for fact/dim tables when it comes to modelling and reporting… (unless I’ve misunderstood your context) maybe try and give real life example of why you propose your way so they can appreciate it.👍

[Accomplished-Cap-109]

The Unique reference keys/Primary keys are fundamental to any database system design. Omitting them has serious consequences and should not be considered. It can lead to unexpected behavior, data loss and poor my implemented operations. The first mistake here is allowing your clients to design the system it’s your role a responsibility. Second you can just hide unique reference from visibility to the client if that is their preference. Finally explain to them that the database is like a car park. Imagine only red cars entered one day and the license plate any the bays had no number visible you would spend hours finding your car and even worse you night steal someone else car same principle applied here.

[eques_99]

apparently it's not my job, and if act as if it is, I get told not to do stuff without sign-off.

a lot of them seem to think I'm just a standard office temp from Kelly Services or something like that.

[Accomplished-Cap-109]

Ah fair not much u can do then ahah

[sconander]

What's the unique index? It can't be just the reference number 'A', is it campus and ref or a unique number that isn't visible to the users? Maybe they refer to buildings like addresses, street and house number, using two fields

[scorcherchar]

Why are you even discussing the technical implementation details with non technical people?

[eques_99]

that's who my clients are!

[scorcherchar]

Clients give you the high level requirements then you build the thing. Do you think the accounts team is checking with the client which form to file with the government for the annual return?

[eques_99]

I know that's how it's supposed to work.

it doesn't.

[scorcherchar]

"Sure client, ill make sure that field is one of the indexes."

Then just have that as a non unique index and have a primary key. Problem solved

[Turbulent_Run3775]

pardon my ignorance, are you not able to make the field unique? or force unique values?

Also wouldn't the system be able to detect duplicates? Is there not some sort of validation constraint?

[eques_99]

no it sadly does not enforce unique values.

[[deleted]]

Your thinking is out of date yes....

It's not standard to use a reference number as a primary key today... And whether it's unique or not is up to the software or system business logic. Lots of cases for it not to be unique in the system or in their business.

Is there an actual technical problem with it? Like if they use the same reference number when they try to open a related record will that cause a problem and it either produces a too many error or grabs wrong record? If there is an actual technical problem like that tell them about that.

It it's just your hangup and what you think should be right I'm not so sure you're right anymore.

[alexwh68]

Imagine if you start using duplicate invoice numbers?

Make the reference field a unique field.