2

I have a table that stores a person's information with close to 10 million rows.

Currently State is a char(2) field on the person table. This leads to tons of duplication of data as you would expect. If I normalize State data into it's own table and create an FK to it in the person table would this result in faster query times?

Before:

SELECT Name, City, State FROM Person WHERE State = 'WI'

After:

SELECT p.Name, p.City, s.Name as State
FROM Person p
    INNER JOIN State s ON p.State == s.Id
WHERE s.Name = 'WI'

It seems to me that this would accomplish an increase in performance but I am far from an expert when it comes to optimizing queries.

Improve this question
14
  • 4
    Indexing your State column may help you better instead of FK I guess. Commented Oct 5, 2012 at 13:09
  • 1
    Since the state column is just a CHAR(2), you seem to have no other data associated with states (e.g. population), and we don't expect states two character codes to change anytime soon, I would hardly even consider making a separate states table "normalization." If you do want to normalize, consider making a separate City table with columns for name and state Commented Oct 5, 2012 at 13:26
  • 1
    @Blam I think 'VA' or 'NC' are a valid key values in this case, and I think it's arbitrary to decide that some numeric key counts a prime key, but a CHAR(2) (which never changes) is not. I don't think there are any more repeating values using a CHAR(2) than if using some numeric foreign key. Commented Oct 5, 2012 at 14:03
  • 2
    @Blam Just to clarify, we could theoretically add a State table with fields code, name, population, area, etc where the code is a primary key of type CHAR(2) using values like 'VA' and 'NC'. But in this case, there seems to be no additional information associated with a state, so there's no actual need for a State table as it would only have the code with no additional fields. Commented Oct 5, 2012 at 14:14
  • 2
    I would tend to design this as having a lookup table for state values that you can use to ensure only valid states are entered, but I would store the two characters with the rest of the address. Commented Oct 5, 2012 at 17:25

6 Answers 6

Reset to default
2

Normalization can result in decreased performance, but rarely if ever will it increase your performance in a case like this, because now the server has to look at two places on disk instead of just one.

Normalization has two purposes:

  1. Reduce the amount of data stored on the disk
  2. Allow data to be updated in a single place

Your query will not benefit from either of these advantages as

  1. there is not much of a difference between a char(2) and an int (the foreign key)
  2. the two digit code for a state will never change, so you will never need to update it
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

That was way too much of a blanket statement to be correct. You can elaborate and explain WHY in THIS case it may be, but that's far from true.
Thanks. I'll remove the downvote, but you may want to consider the fact that normalization has more than just two purposes. : )
@Norla I think the point is that James doesn't agree with you that normalization has more than two purposes.
If that is an active database the State table would stay in memory. Why would you use int as the fk when the are only 50 states? Int would result in more twice the data on disk.
1

If you are only talking about 2 characters - then probably not much use in splitting that to a new table.

(however - consider that there is a specific domain of values allowed - what if someone enters VX or some other non allowed value - there is no way to constrain properly or efficiently when denormalized)

if you are talking about other information, like the US postal abbraviation (2 characters) and the states full name, and maybe some other information, then absolutely yes - split it apart.

as a matter of practice, you should always err (imo) on the side of proper normalization - then after a lengthy argument only should you consider denormalizing.

Improve this answer

Comments

1

If you create a very narrow key on the state table, like TINYINT, then you can have increased performance, but it's not guaranteed. It's totally worth testing though.

Consider making duplicates of each table, indexing them properly, then running queries on both at the same time using the analyzer.

You may end up with a 1% increase in speed.

Still, normalization is rarely a bad idea...

Improve this answer

Comments

0

There is just not much space to save.
You could use byte (tinyint) for the primary key on the state table as there are only 50 states.
Char(2) is two bytes.
So you would only save one byte per line in the person table.

The advantage to compact data is less disk space and also less memory.
For a fixed amount of memory you have a higher chance of the data in memory if the data is smaller.

I don't think a size difference of 1 byte it worth the overhead of the join.

But I would normalize just because it is a good practice.
Why let someone put in state of JZ?

Fails 3rd normal form
Achieve 3NF
If there are repeating groups, separate the groups into relations of their own.

You could use char(2) as the PK in the state table so there is a direct match of char(2) to char(2).
That would satisfy 3rd normal form as the values are restricted to the 50 states.
And in that case you would not need to join on a select as the actual value is in the primary table.
On an insert or update the FK relationship is enforced so it has to be a valid state.

If you wanted to report the whole state you could add a column to the state table for the full name.

Improve this answer

Comments

0

Well.. in your query you are doing something non optimal.

SELECT p.Name, p.City, s.Name as State
FROM   Person p
       INNER JOIN State s ON p.State == s.Id
WHERE s.Name = 'WI'

You get ALL the person details that have a record in ALL state, afterwards you are filtering the state with 'WI'..

If you would try this, you will get less state!

SELECT p.Name, p.City, s.Name as State
FROM   Person p
       INNER JOIN State s 
         ON  p.State == s.Id
         and s.Name = 'WI'

Why? Because you will only get the state colums where name is WI instead of ALL state columns.

After that.. if it would fit with your indexes, make a filtered index on state where name = 'WI'

That would help some..

Improve this answer

Comments

-2

Searching on string indexes is (in theory) much slower than searching on int indexes. Which means yes; normalizing your data would make it faster. In practice, I've often seen the difference to be minimal; YMMV. You may have to pre-cache or separately select the state ids:

SELECT p.Name, p.City, s.Name as State
FROM Person p
INNER JOIN State s ON p.State == s.Id
WHERE s.Id = (select id from State where State.Name = 'WI');

As always with queries, it is best to test and optimize.

Improve this answer

1 Comment

How can this be better than where S.Name = 'WI' ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.