3

I'm 'inherithing' the job of a database admin for a very small team (3-4 people) and while I know how to execute SQL queries relatively well and understand the basics of programming efficiency , I still new in the realm of thinking about database architetures.

I noticed the previous admin, build this weird schema that I`m still trying to figure out the benefits. Since he's more experienced than I in DB architecture there must be some positives to it. Unfortunately he's no longer around to ask for his motivations.

In essence, whenever there would be a character column whose values repeat often (say, the salesman_name in table of sales), this character column would be replaced by an index (salesman_id), functioning as a foreign key to another table. This second table would only contains the foreign key (salesman_id) and list of unique character values (salesman_name). There are no other tables that use this character column.

Why to do that?

I could understand this practice if there were other tables linking to salesman_name, so an update to salesman_name can be done in one single place. I could also understand in terms of saving disk space since less bytes are replicated every row. However our sales table has "only" 5 million rows, and even in csv format it has less than a 1Gb in size. And while it saves on disk, it requires a Join everytime we want to look at the fully fledged sales table. This happens so often that the previous admin even set a view with this join already done. But why to separate solely to unite later? Is there any other obvious reason to separate repeating data that I'm missing?

3
  • That's called "normalisation" (or "normalization" in the US); look it up.
    – mustaccio
    Commented Dec 9, 2022 at 17:21
  • It woukd be clearer if you are able to use a dbfiddle to post your schema Commented Dec 9, 2022 at 21:48
  • I think @mustaccio answered the question pretty well. The original dabatase admin was normalizing it. Thanks!
    – JMenezes
    Commented Dec 12, 2022 at 9:03

2 Answers 2

3

Most relational databases use paged storage. The unit of IO as far as the DBMS is concerned is 1 page. Each page will typically hold multiple rows. Each row fits entirely within a single page.

When a row is needed for a query the whole of its page is read in. All of the other rows on that page can be used for other queries without incurring further IO cost. So the more rows that fit on a page the less IO there will be and the faster the system runs.

There are many other technologies and implementations than that but the above covers many typical cases.

One way to fit more rows on a page is to make each row shorter. Replacing names (15-40 bytes?) with an int (4 bytes) will help with this. The int to string mapping will be a separate table. Since there are relatively few distinct string values this mapping table will be small. On balance there will be a net reduction in IO.

You can think of this as a form of dictionary compression. Arguably it is also more normalised as correcting a typo will update one column in one row avoiding the risk of inconsistent data. I'm less convinced that swapping a natural key for a surrogate key counts as normalisation.

Yes there must be joins to return a full sale row. The data to join is more likely to be in memory and DBMS are optimised for joins, so it's likely to be faster than that equivalent disk access.

If all the tables fit comfortably in RAM with enough left over for query execution then space minimisation isn't worth the effort. Perhaps when the system started out it ran on a 286 with a 500Mb disk when it was worthwhile? Perhaps it's a habit the designer carried from past experience and never thought to question.

0

... whenever there would be a character column whose values repeat often (say, the salesman_name in table of sales), this character column would be replaced by an index (salesman_id), functioning as a foreign key to another table. This second table would only contains the foreign key (salesman_id) and list of unique character values (salesman_name). There are no other tables that use this character column.
Why to do that?

One word answer - Normalisation
You have one copy of the Salesman's name, held in the separate table (a.k.a. "Single Source of Truth").
As you say, you will have many sales records for that Salesman and each of them will have only the [foreign key] Identifier of the Salesman's record.
This is exactly how Relational Databases should be built.

Other reasons:

A Salesman can "exist" without having any Sales.
New starters, who haven't sold anything yet, have to be "in the system" somehow. You can add Salesman records without affecting anything else.
Anything that can exist "on its own" needs its own Table.

People change their names.
If you have the actual name embedded in lots of sales records, then changing all those records could be a huge operation for your database - lots of transaction logging, disk writing, etc. It's expensive. With the data properly-normalised like this, you need change only one field in one record.

Over time, you might want to store more than just the Salesman's name in that table. With it "split out" like this, adding extra columns to the Salesman table is simplicity itself. Without it, you'd have to "bloat" the sales table, and that could come back and "bite" you in new and unexpected ways.

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.