0

I'm designing a new database schema, and every time I do, I like to check my assumptions on normalisation. Something I haven't been able to find a definitive answer for here or by searching the web is how to handle multi-level hierarchies. Best way to explain is by example.

Source Data

ref, state, city, suburb, population
-------------------------
1, ABC, x, qwe, 1234 
2, ABC, y, rty, 1456
3, DEF, z, uio, 2000 

If I were just normalising that at face value, I'd create four tables - Residents, States, Cities, Suburbs. However, let's add the stipulation that there's a hierarchy of State->City->Suburb.

Now in order to preserve referential integrity, it should look something like:

states
-------
stateid, statename

cities
-------
cityid, stateid, cityname

suburbs
-------
suburbid, cityid, suburbname

The question I haven't been able to answer is what should the Residents table look like? The sensible option is:

ref, stateid, cityid, suburbid, population

and the slightly less sensible option is:

ref, suburbid, population

So when it comes to normal forms, I don't think the second answer is a valid normal form. Even though suburbid seems like a valid super key, it would involve traversing upward through the hierarchy to retrieve city and state names.

But then, I'm not sure the first option is valid either, because there's redundancy throughout the hierarchy - I can get the state name three ways (Residents.stateid, Cities.stateid, Suburbs.cityid->Cities.stateid)

From a functional standpoint, the joins would be something like:

--First Option
SELECT [...] 
FROM Residents AS r
JOIN States AS s ON s.stateid = r.stateid
JOIN Cities AS c ON c.cityid = r.cityid
JOIN Suburbs AS b ON b.suburbid = r.suburbid

--Second Option
SELECT [...] 
FROM Residents AS r
JOIN Suburbs AS b ON b.suburbid = r.suburbid
JOIN Cities AS c ON c.cityid = b.cityid
JOIN States AS s ON s.stateid = c.stateid

What I'm hoping to understand is many faceted:

  • Is there a better approach from a normalisation perspective?
  • Is there a better approach from a performance perspective? (Assume MSSQL if relevant)
  • Is there a reason to favour normal forms over performance, given that the underlying schema already preserves referential integrity?
  • Are there other options I should consider? Have I got it all wrong?

Thanks in advance!

2
  • It seems to me, that Population is directly related to Suburb - why not make it simply attribute of the Suburb? Or do you differentiate various populations in single Suburb?
    – J. Doe
    Commented Jul 3, 2023 at 7:15
  • @J.Doe - fair point! That's a flaw in my example. I shouldn't have picked a singular measure like population. It probably should have been something more indirect, like each ref is a civil project, and the measure is expenditure. That way, it's related to the suburb, but not an attribute of the suburb.
    – Vocoder
    Commented Jul 3, 2023 at 23:52

1 Answer 1

0

So when it comes to normal forms, I don't think the second answer is a valid normal form.

It is indeed Valid normal form.

I'm not sure the first option is valid either, because there's redundancy throughout the hierarchy

This is known as Denormalization. You have to be extra careful while INSERT/Update to main referential integrity.

ref, stateid, cityid, suburbid, population

Best way to decide is to create all possible query for this table.

i) You need Residents of particular state,along with other detail like CityName,Statename,SuburbName

ii) You need Residents of particular City or city and state,along with other detail like CityName,Statename,SuburbName

and so on.In each case you need to join all 3 tables.

But may be I need to know only sum of population of particular state and so on. In this scenario all 3 tables Join is not require.

I will go with this option

ref, stateid, cityid, suburbid, population

and use this query

--Second Option

SELECT [...] 
FROM Residents AS r
JOIN Suburbs AS b ON b.suburbid = r.suburbid
JOIN Cities AS c ON c.cityid = b.cityid
JOIN States AS s ON s.stateid = c.stateid

This is bad design,

ref, statename, cityname, suburbname, population

This neither normal form nor Denormalisation.This is bad design

1
  • Thanks for the response @KumarHarsh - looking back, I can see my example is flawed, since population is an attribute of Suburb and should be structured as such. That said, your mention of Denormalisation has put me on the right path. My brain had been processing the term "denormalisation" as "non-normalised" or "reversing normalisation" as opposed to adding redundancy to optimise a normal form.
    – Vocoder
    Commented Jul 4, 2023 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.