Is better to copy value between DB table or create a reference?

  • 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

  • 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.

Suggested Topics