0

I have been working on my database and the thought occurred to me that maybe it would be better to combine two of my tables to better organise the data and perhaps get performance benefits (or not?).

I have two tables that contain addresses, delivery and the other invoice, their structure is identical. One table contains invoice addresses and the other contains delivery.

What would be the implications of merging these together into one table simply called "addresses", and create a new column called addressTypeId? This new column references a new table that contains address types like delivery, invoice, home etc.

Is having them how they are now, separate, better for performance as requests for the different types of addresses (delivery and invoice) make use of two tables as opposed to one table which might mean delays when requesting address data?

By the way I am using INNODB.

imperium2335
  • 23,402
  • 38
  • 111
  • 190

4 Answers4

1

If you are missing the appropriate indexes, then the look up performance will drop by a factor of two (if you are merging two equally sized tables). However, if you are missing indexes, you likely don't care about the performance. Lookup using a hashed index is constant-time. Lookup using a tree index is logarithmic, so the effect is small. Writes to a tree index are logarithmic as well and writes to a hash map are amortized constant.

don't suffer from premature optimization!!!

A good design is more important than peak performance. Address lookup is likely not your bottleneck. A bad code resulting from a bad database design far outweighs any benefits. If you make two tables, you are going to duplicate code. Code duplication is a maintainance nightmare.

Merge the tables. You will be thankful when you need to extend your application in the near future. You could want to make more address types. You could want to add common functionality to the addresses (formatting). Your customers will not notice the extra milisecond from traversing one more level of a binary tree. They will notice you have a hard time adding an extra feature and they will notice inconsistencies arising from code duplication.

You might even gain performance by merging the tables. While you might need to traverse an extra node in a tree, the tree might be more likely to be cached in memory and not need disk access. Disk access is expensive. You might reduce disk access by merging.

As @BenP.P.Tung already said, you don't need an extra table for an enumeration. Use an enumeration type.

John Dvorak
  • 26,799
  • 13
  • 69
  • 83
1

their structure is identical.

Are their constraints identical as well?1

  • If yes, merge the addresses.
  • If no, keep them separate.

Constraints are as much part of the table as are its fields.

Is having them how they are now, separate, better for performance as requests for the different types of addresses (delivery and invoice) make use of two tables as opposed to one table which might mean delays when requesting address data?

Do you query both kinds of addresses in the same way?

  • If yes, it shouldn't matter either way (assuming you indexed correctly).
  • If not, then different tables enable you to index or cluster your data differently.

Related posts:

1 For example, are both delivery and invoice supposed to be able to reference (through foreign keys) the same address? Are PKs of addresses supposed to be unique for all addresses or just for addresses of particular type? Are there any CHECKs that exist for one address type and not for the other? Etc, etc...

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

If you just need to distinguish the address difference. I suggest what you need is a ENUM column in this merged table. If it is exist, you can add a new column like following,

alter table add addressTypes ENUM('delivery','invoice','home') DEFAULT NULL;

Or DEFAULT invoice something you think should be default when you can not get the required information.

Don't need to put all enum values at a time. Just what you needed now, and add more value in the future as following.

alter table change addressTypes addressTypes ENUM('delivery','invoice','home','office') DEFAULT NULL;

Ben P.P. Tung
  • 1,224
  • 13
  • 21
0

One table will work fine. If there is a performance concern, then add the address type column to the primary index at the start of the index. This will avoid any performance issues until you have a very large number of addresses.

emperorz
  • 429
  • 3
  • 9