DB design: Should I use constraints within a table or a new table

I inherited a large existing DB and I'd like to know if I should refactor it because 95% of my queries require joining at least 4 tables.

The DB has a 5 tables that only have an ID and Name column with less than 20 rows. I assume the author did this so he could change the names there and not change them in the other tables, but many of those tables are only referenced in one other table. Should I refactor these small 2 column tables into the a larger table and add a constraint to t开发者_Python百科he column so users can't input incorrect names instead of having seperate tables?

---

Resist that urge. From your description I can deduce that the existing design is solid and probably well normalized. Your refactoring may actually undo a good db structure.

If you are bothered by writing a lot of joins in your queries I would suggest creating views to mitigate the boilerplate.

...the author did this so he could change the names there not change them in the other tables...

That is evidence of good design and exactly what you should strive for in a normalized database.

---

no.

your db is normalized and proper. and you save space, lookup time, indexing for storing an int rather then a varchar name

small tables are optimized away if they are properly keyed.

---

Sounds like what you have are lookup tables. Let me tell you waht happens when you decide to put all lookups in one table with an additonal column to specify which type it is. Fisrt instead of joining to 4 different tables in one query, you have to join to the same table 4 times. There ends up being more contention for the resources in the "one table to rule them all". Further, you lose FK constraints. That means you eventually lose data integrity. So if one lookup is state, nothing wil prevent you from putting the id values for a different lookup for customer type in the stateid column in the customeraddress table. When the lookups are separate you con enforce that relationship.

Suppose instead of one big table you decide to have a constraint on the column for customer type. Constraints are now enforced but you have a problem when they need to change. Now you have to alter the database in order to add a new type. Again usually this is a very bad idea espcially when the table gets large.

---

Short story: Replacing strings with ID numbers has nothing to do with normalization. Using natural keys in your case might improve performance. In my tests, queries using natural keys were faster by 1 or 2 orders of magnitude.

You might have accepted an answer too quickly.

The DB has a 5 tables that only have an ID and Name column with less than 20 rows.

I'm assuming these tables have a structure something like this.

create table a (
  a_id integer primary key,
  a_name varchar(30) not null unique
);

create table b (...
-- Just like a

create table your_data (
  yet_another_id integer primary key,
  a_id integer not null references a (a_id),
  b_id integer not null references b (b_id),
  c_id integer not null references c (c_id),
  d_id integer not null references d (d_id),
  unique (a_id, b_id, c_id, d_id),
  -- other columns go here
);

And it's obvious that your_data will require four joins (at least) to get usable information from it.

But the names in table a, b, c, and d are unique (ahem), so you can use the unique names as targets for foreign key references. You could rewrite the table your_data like this.

create table your_data (
  yet_another_id integer primary key,
  a_name varchar(30) not null references a (a_name),
  b_name varchar(30) not null references b (b_name),
  c_name varchar(30) not null references c (c_name),
  d_name varchar(30) not null references d (d_name),
  unique (a_name, b_name, c_name, d_name),
  -- other columns go here
);

Replacing id numbers with strings doesn't change the normal form. (And replacing strings with id numbers doesn't have anything to do with normalization.) If the original table were in 5NF, then this rewrite will be in 5NF, too.

But what about performance? Aren't id numbers plus joins supposed to be faster than strings?

I tested that by inserting 20 rows into each of the four tables a, b, c, and d. Then I generated a Cartesian product to fill one test table written with id numbers, and another using the names. (So, 160K rows in each.) I updated the statistics, and ran a couple of queries.

explain analyze
select a.a_name, b.b_name, c.c_name, d.d_name
from your_data_id
inner join a on (a.a_id = your_data_id.a_id)
inner join b on (b.b_id = your_data_id.b_id)
inner join c on (c.c_id = your_data_id.c_id)
inner join d on (d.d_id = your_data_id.d_id)
...
Total runtime: 808.472 ms

explain analyze
select a_name, b_name, c_name, d_name 
from your_data
Total runtime: 132.098 ms

The query using id numbers takes a lot longer to execute. I used a WHERE clause on all four columns, which returns a single row.

explain analyze
select a.a_name, b.b_name, c.c_name, d.d_name
from your_data_id
inner join a on (a.a_id = your_data_id.a_id and a.a_name = 'a one')
inner join b on (b.b_id = your_data_id.b_id and b.b_name = 'b one')
inner join c on (c.c_id = your_data_id.c_id and c.c_name = 'c one')
inner join d on (d.d_id = your_data_id.d_id and d.d_name = 'd one)
...
Total runtime: 14.671 ms

explain analyze
select a_name, b_name, c_name, d_name 
from your_data
where a_name = 'a one' and b_name = 'b one' and c_name = 'c one' and d_name = 'd one';
...
Total runtime: 0.133 ms

The tables using id numbers took about 100 times longer to query.

Tests used PostgreSQL 9.something.

My advice: Try before you buy. I mean, test before you invest. Try rewriting your data table to use natural keys. Think carefully about ON UPDATE CASCADE and ON DELETE CASCADE. Test performance with representative sample data. Edit your original question and let us know what you found.