3

A sql database I am working on at a customer has been created with an unusual structure.

For example, instead of having one table for each of the following:

  • Country,

  • State,

  • City,

  • Units (in, m, miles per hour),

they only have two tables:

  1. One for the list id (fields: list index and list name). Ex of records: City, Units
  2. One for the items in a given list (fields : item index, description of the listitem and a fk referring the related list index stated above). Ex of records: km/h, Atlanta, miles, Paris

The sequences for ids in the listitem table are organized as such that there are gaps to somewhat separate the items from different groups in the listitem, in case one need to add a new item in the list:

  • Seq for Unit may start at 20000

  • Seq for City may start at 30000

So if 10 new Units need to be added , the index could stay grouped with other already created Units and without overflowing in other items like city. Application layer deals with the sequence range for each groups of items.

They have 30+ such lists in the list table and thousands of item in the item table each relating the a list id.

I think this is non sense for multiple reasons. But they think it is efficient because 'we have just 2 tables instead of 30'. They also think it is novel and a somewhat typical way to work. I understand this can work, technically, but I anticipate unnecessary and multiple problems with the implementation of these 2 tables.

My question: is it typical or an accepted practice to do something like the above?

3
  • 1
    Sounds like a form of EAV - I would strongly urge you to refactor properly!
    – Vérace
    Apr 14, 2022 at 4:20
  • 1
    Related: dba.stackexchange.com/a/138535/36809 Apr 14, 2022 at 8:35
  • @Vérace the rest of the structure is proper I think. Just this part - which is quite important and involved everywhere anyway. Will definitely try to take the refactor option seriously.
    – marsisalie
    Apr 14, 2022 at 12:39

1 Answer 1

3

This is a known pattern, but not a good one, and not novel. While the application can be made to run this way you miss out on a lot of benefits.

First, foreign keys. It is not possible to declare a reference to the list table "but only IDs in the 10,000 range." So proper referential integrity is gone. There's nothing to stop a product weighing 10 Londons and being coloured Frankfurt.

Second, data types. All the list values data types must be the lowest common denominator. In practice this means Unicode. Again, there's nothing to stop a vehicle's passenger count being one of {1, 2, 3, Tuesday}.

Similarly the length must be the maximum imaginable not just the longest known. This has overheads in storage, memory consumption etc.

Third, you may end up with only one table but there are still 30+ entity types shoehorned into that table. These have not gone away. This adds an unnecessary cognitive load. Instead of looking for a city in a table called Cities you have to remember it is in a table called Lists, between 10,000 & 15,000 (or was that 20,000 to 30,000?).

Fourth, joins lose self-documentation. Instead of

... Data as d
inner join Cities as c on c.CityId = d.CityId
inner join Units as u on u.UnitId = d.UnitId

we instead have

... Data as d
inner join Lists as l1 on l1.ListId = d.CityId
inner join Lists as l2 on l2.ListId = d.UnitId

I know which I prefer.

Fifth, with separate tables each table's key could likely be a 1 byte integer for 256 values. When altogether I'll bet it will be a 4 byte int. For sure not a whole bunch of bytes overall. But why get worked up about reducing the table count and not the byte count?

I'll stop here and end with this observation. No DBMS worthy of the name will care two hoots about 30 fewer tables in its catalog. It will make absolutely zero difference to runtimes but will haunt maintenance for years to come.

4
  • Intuition also tells me that query performance will generally be worse because of multiple issues you mentioned interfering with the database system from taking advantage of optimizing and picking the best execution plan. 🙂 Let alone the loss of flexibility in query-ability. I'd also implore OP's customer to check out some enterprise software's databases (e.g. Microsoft's ERP systems) that have tables into the tens of thousands and re-evaluate if 30 is ok lol.
    – J.D.
    Apr 14, 2022 at 11:28
  • @J.D. Definitely, they are missing a lot. Already forced to make complicated and un-intuitive queries and hacking around for the simplest things. Great arguments.
    – marsisalie
    Apr 14, 2022 at 13:02
  • @MichaelGreen I had some issues listed in my mind when redacting the post and you raised a few that I did not thought about. Will add to the 'why it is not so good argument master list'. 3 and 4 are the one that I worry the most. This is for a long term project and a lot of people will work work with this; efficient maintenance is a criterion; as is it will not be easy unless changes are made.
    – marsisalie
    Apr 14, 2022 at 13:13
  • 1
    3 & 4 you can work around with views. Lack of reliable foreign keys could cripple the application on day one. Apr 14, 2022 at 13:20

Your Answer

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

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