2

Should the repeating values in the "Sales" table below be separated out into a second table as part of the process of database normalization? Note that none of the values are functionally dependent on each other; those values that are functionally dependent are already normalized out to other tables. I'm just not sure if there's any benefit to separating the "clusters" of values in different domains out into a separate table since the table contains tens of millions of rows, but only a few thousand distinct combinations of these values:

LOCATION     PRODUCT         RECIPIENT     DATE       AMOUNT  
New York     Cheesecake      Bill          1/1/2000   $25.00
New York     Cheesecake      Bill          2/1/2000   $42.00
New York     Cheesecake      Bill          3/1/2000   $17.00
Dallas       Cheesecake      Bill          4/1/2000   $15.00
Dallas       Cheesecake      Bill          5/1/2000   $17.00
Dallas       Cherry Pie      Bill          6/1/2000   $14.00
Dallas       Cherry Pie      Bill          7/1/2000   $13.00
Dallas       Cherry Pie      Sam           8/1/2000   $16.00

The table would be separated into a "Sale Types" table:

SALETYPE     LOCATION    PRODUCT       RECIPIENT
1            New York    Cheesecake    Bill
2            Dallas      Cheesecake    Bill
3            Dallas      Cherry Pie    Bill
4            Dallas      Cherry Pie    Sam

So that the original "Sales" table would look like this:

SALETYPE     DATE        AMOUNT
1            1/1/2000    $25.00
1            2/1/2000    $42.00
1            3/1/2000    $17.00
2            4/1/2000    $15.00
2            5/1/2000    $17.00
3            6/1/2000    $14.00
3            7/1/2000    $13.00
4            8/1/2000    $16.00

Since each unique combination of values appears approximately 1,000 times each on average, I believe that would save storage and potentially memory pressure, but my understanding of the underlying RDBMS memory optimizations is admittedly pretty limited. Would that be considered database normalization though?

2
  • So what is the PK on this table, what is unique? Is there a PK on this table? Commented May 2, 2020 at 12:00
  • How is this not just asking for yet another presentation of normalization from absolute scratch? Please follow a textbook & ask 1 specific question where you are 1st stuck. See help center & the voting arrow mouseover texts.
    – philipxy
    Commented May 3, 2020 at 2:05

2 Answers 2

1

There is no normal form which would have you move those columns to a separate table. Normalization is a process in the logical design of tables that discusses functional dependency between a table's columns. If there is no un-resolved functional dependency there is no more normalization to perform.

Further, a logical table represents a class of objects, events or ideas from our business domain. There is a real-life thing that the table holds data for. What is that thing that your proposed "Sales Types" table would hold? Likely your business users would shrug at this question.

That said, during the physical implementation (distinct from the logical design before) you may choose to move columns between tables for reasons other than functional dependency. This can be confusing because the end results look the same - columns moved to new tables. The difference is why we move these columns. This will be important later when change happens. We need to know why we did someting so we will know why we may choose to undo it. We undo normalization if the business rules change. We undo physical implementation if the desired performance profile changes.

In the context of physical implementation your proposed table looks like a dictionary encoding scheme. There is nothing wrong with doing this. If your application benefits overall from this extra development work then go fot it.

Many DBMS offer compression natively. Column-oriented storage is particulary effective at this if your product offers this choice. By using native features you gain the performance benefits you seek without taking on the development and maintenance cost. I'd recommend you stress-test any native feature to see if it can deliver your desired performance.

-1

Welcome.

If you notice, after your creation of "Sales Types", there are too many duplicate values within. So,...one essential improvement is

Let the Sales table have

LocationId ProductId RecipientId Date Amount

Location table

LocationId Location

Product table

ProductId Product

Recipient table

RecipientId Recipient

Other than this, instead of sticking to normalization theory strictly, you may have to consider your requirements too.

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.