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:
- One for the list id (fields: list index and list name). Ex of records: City, Units
- 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?