0

I’m at a crossroads, and I don't know which is the best option to choose.

Let's say I have a Table A which contains addresses and other information. As you can expect lot of rows contains the same addresses (but the other info can be different). Then I have a table B which contains only addresses. Which is the best option:

  1. Remove the address data from table A and add a foreign Key that references the corresponding address in the table B
  2. Keep everything as it is and every time a new row of A is created the address data are copied from table B to A

Solution 1 let me save space and ensure me consistency since the data are bind. On the other hand if I change a row in table B all the row of A are affected. Moreover I need to change lot of code (e.g. previous code tableA.row[0].address became tableA.row[0].TableB_reference.address), so it can lead to lot of bugs.

Solution 2 get more DB space since data are duplicated and data can be "corrupted" or became not consistent. On the other hand, if at certain point in time I change a tableB row only the new rows of tableA will be affected, without changing the "old" rows. Moreover, it doesn't require to change lot of code (tableA.row[0].address remains valid code)

What do you suggest? Is there any different solution?

Thanks in advice

2 Answers 2

1

It depends greatly on the purpose of the two tables. For example, if table A is about shops in a city, and table B is about customers in a city, those two addresses are completely unrelated and it wouldn't make any sense to link them. In a different scenario, if table A is about employees of a company, and table B is about the members of the company's football team, then each football player is also an employee and having duplicated addresses is a) redundant and b) prone to data discrepancies.

0

Redundant information in a database is a no-no.

That guideline tells you which way to go.

(There are cases where performance requires violating the guideline. When you have a table with millions of rows, come back with some slow queries to get help on.)

As for the JOIN required to get to the "normalized" data, that is usually not a big deal. And the space saved is somewhat important. And the ability to change a value for all occurrences may be a benefit.

Do not normalize numbers or dates. Any attempt to do a range check on such is terribly inefficient.

Provide a specific case if you want to see if it is OK to violates the guidelines I just gave you.

4
  • Thanks @LionCareto and RickJames. My case is the following: table A contains delivery instances, that means someone need to deliver something at a specified address, time, with some cost etc.... Table B would be like an address book, because lot of times the delivery places are the same Commented Apr 10, 2022 at 7:32
  • My greatest doubt is: if a row in table B changes then the past deliveries will change address (if the tables are linked), and I wouldn't like to loose information about old location delivery. One solution I thought is not to change table B rows but always create a new, so that past deliveries remain valid. Commented Apr 10, 2022 at 7:38
  • @AlessandroSalvetti - Agreed. A more important example of the same concept -- when recording an "order", where a product was sold for a specific price, that price should not be normalized. The price is a valuable record of the transaction. The technique becomes: "never change an entry"; instead, if the same value occurs, link to it; else create a new value. Then Normalization is only for "de-dupping", hence saving disk space.
    – Rick James
    Commented Apr 10, 2022 at 16:21
  • Thanks Rick James for help Commented Apr 11, 2022 at 6:14

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.