3

I'm working on converting a legacy system into a more modern web application and I keep finding myself tripping over normalizing the data.

The dataset is quite large; about 30 tables with around 15 columns and up to 16 million records each.

There aren't relationships or many of the modern SQL concepts as this is coming from a hand-rolled database built in the 90s. So no identity column, no foreign keys, no constraints, etc.

The one pseudo-relationship is between all of these 'data' tables and a massive codes table (definitely a MUCK situation) which has the translations of not only all the codes in the data but also the codes of the HEADER NAMES as well as those are important to the end user too.

Let's look at a few example tables:

Address Table

| CID  |        SAD1        |  SAD2  |   CTY   | ST | CON |  ZIP  |
|------|--------------------|--------|---------|----|-----|-------|
| AA11 | 314 Hamilton Beach |        | Denver  | CO | USA | 80209 |
| ABC3 | 487 Fox Ave        | APT 2  | Toronto | ON | CAN | X0E   |

Orders Table

|      OID      | IN   | PKG_REQ |
|---------------|------|---------|
| 1458-123-4564 | 0011 | Z       |
| 5349-543-0987 | 0A05 | K       |
| 5349-983-9845 | FD67 | K       |

Codes Table

|   CODE    |          VALUE           |                                           DEFINITION                                           |
|-----------|--------------------------|------------------------------------------------------------------------------------------------|
| CID       | Customer identifier      | A unique customer identifier                                                                   |
| CTY       | City                     | A large town                                                                                   |
| CON       | Country                  | A nation with its own government                                                               |
| CON:USA   | United States of America | A country in North America with a population of around 340 million people                      |
| CON:CA    | Canada                   | A country in North America with a population of around 33 million people                       |
| IN        | Item Name                | A descriptive name for an item                                                                 |
| IN:0011   | Scissors                 | An instrument used for cutting                                                                 |
| IN:0A05   | Hammer                   | An instrument used for hammer time                                                             |
| IN:FD67   | Knife                    | An instrument used for slicing                                                                 |
| SAD1      | Street Address 1         | Bacon ipsum dolor amet brisket burgdoggen t-bone andouille turducken prosciutto doner capicola |
| SAD2      | Street Address 2         | Salami beef ribs frankfurter pork tri-tip hamburger shank                                      |
| ST        | State                    | Bacon ipsum dolor amet brisket burgdoggen t-bone andouille turducken prosciutto doner capicola |
| ST:CO     | Colorado                 | Bacon ipsum dolor amet brisket burgdoggen t-bone andouille turducken prosciutto doner capicola |
| ST:ON     | Ontario                  | Bacon ipsum dolor amet brisket burgdoggen t-bone andouille turducken prosciutto doner capicola |
| OID       | Order ID                 | The unique identifier for the order                                                            |
| PKG_REQ   | Packaging Requirements   | The requirements for packaging the order                                                       |
| PKG_REQ:K | No special requirements  | No special packaging requirements needed                                                       |
| PKG_REQ:Z | Fragile                  | Order is fragile and special care needs to be taken                                            |
| ZIP       | ZIP Code                 | ZIP Code for address                                                                           |

So a code without a colon decodes the header whereas a code with the colon decodes the value that's in the header column.

The codes are still important to the end user but I would like to be able to show a decoded view as well.

Both

|      OID      | IN   | PKG_REQ |
|---------------|------|---------|
| 1458-123-4564 | 0011 | Z       |

and

|   Order ID    | Item Name | Packaging Requirements |
|---------------|-----------|------------------------|
| 1458-123-4564 | Scissors  | Fragile                |

with possibly a tooltip or something displaying the definition.

I've gone the path of denormalizing (I know generally not the best but since hardly any of this data is normalized I thought it might be the simplest) So something like:

|  IN  | IN_HEADER | IN_DECODED |         IN_DEFINITION          |
|------|-----------|------------|--------------------------------|
| 0011 | Item Name | Scissors   | An instrument used for cutting |

But that turns a table that's 15 columns wide into 60 and balloons the database at least 10x...

Question: How best do I normalize/organize this data so that it more falls in line with modern best practices?

Do I split the massive codes table into separate state_codes country_codes tables (even if that creates 400 or so lookup tables) or keep them in the same table?

Should I remove the code from the original table all together and just implement foreign keys with an arbitrary ID and then have the code and decoded information all in another table?

Probably the biggest hurdle in my mind is getting the value and definitions for the headers. How do I make that relationship in a nice SQL way? (We're using MS SQL Server if that makes a difference.)

I'm hoping there's a simple answer out there and I'm just too close to see it right now.

Thanks for the help!

2
  • Its hard to make suggestions without an example. But you are definitely getying confused. There are no colons used in the address table. The CON is just country with two values. That part is already normalised. Commented Jul 7, 2023 at 21:25
  • @RohitGupta Thanks for the reply. Let me know if I can expand on my examples in a more helpful way. In my example to translate CAN into Canada you have to know the text for the column name is CON concat that with :CAN and query the codes table for SELECT TOP 1 VALUE FROM CODES WHERE CODE = 'CON:CAN'; So that maybe technically normalized but doesn't seem like the best practice to represent that relationship.
    – Kenny Read
    Commented Jul 7, 2023 at 22:06

0

Your Answer

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