Whether to Split Data in to Separate PostgreSQL Table

Question

I am creating an app with a WPF frontend and a PostgreSQL database. The data includes patient addresses and supplier addresses. There is an average of about 3 contacts per mailing address listed. I'm estimating 10,000 - 15,000 contact records per database.

When designing the database structure, it occurred to me that rather than storing mailing addresses in a single "contacts" table, I could have one table storing names and other individual data, with a second table holding addresses. I could then create a relationship between the tables, to match addresses with contacts.

I have a pretty good idea how I can neatly organise situations such as changing the address of a single contact, where the other contacts are staying at the same address.

The question is: is it worth it? Can I expect to save much in the way of storage size? Will this impact the speed of queries adversley? How about if I was using something other than PostgreSQL?

Answer 1

Unless you think a large number of your users will be sharing addresses and they'll be often changing, I don't see the need to normalize out the address portion. In the various places I've worked and see users tables, sometimes it is, sometimes it isn't - never really seemed to make a terrible amount of trouble one way or another.

In terms of performance, with just 10-15k records and proper indexes, I can't imagine you'd notice too much difference one way or the other on modern hardware (although technically the separate table should be slower).

Answer 2

I would strongly suggest normalizing this. You never know what kind of trouble you will run into. LedgerSMB has a relatively decent entity/user/contact/location schema that creates a very flexible environment. You can see it here (starts at line 363):

http://ledger-smb.svn.sourceforge.net/viewvc/ledger-smb/trunk/sql/Pg-database.sql?revision=3042&view=markup

Answer 3

I agree with Joshua. Once it's set up properly (normalized) it's very easy to manage any changes in your app in the future.