CODEWITHSUNDEEP
database
John Alexander Betts
John Alexander Betts

Reputation: 5204

Inconsistent data in table

A partner where I work have created the customer table with the following fields:

first_name, middle_name, last_name, second_last_name, full_name

Where full_name is the concatenation of the other fields.

Can you give me the best explaining why is a bad practice?

Upvotes: 1

Views: 49

Answers (2)

Robert Siemer
Robert Siemer

Reputation: 34707

If the full_name is persisted on storage, then you got data doubling: you waste twice the storage with no benefits and extra overhead on editing or other maintenance.

If full_name column is actually a function (e.g. it is calculated from the other elements on this row) the solution is fine!

Depending on the database engine you use those calculated columns can only be read (you have to update the other columns to change their outcome), or even be read-write. Writing to such a column is handled by another function, which for example could parse the full name and store the parts in the row, which is to be updated.

Upvotes: 0

Joe Farrell
Joe Farrell

Reputation: 3542

It's not ideal because sooner or later, someone or something is going to update last_name or first_name without updating full_name, or vice versa, and you'll have something like this in your database:

first_name   last_name   full_name
John         White       John Black

And then you get to try to figure out where the discrepancy is coming from and what this guy's last name is really supposed to be, which is no fun. If you're going to denormalize a table like this, there ought to be some compelling reason for doing so. What's your partner's rationale for wanting full_name to be a separate field?

You should probably investigate alternatives. For instance, you could define a view that returns the various name components from your table and also assembles them into a full_name. Depending on your RDBMS, you may have other options as well. For instance, in SQL Server you can put a computed column right into your table.

declare @customer table (first_name varchar(50), last_name varchar(50), full_name as first_name + ' ' + last_name);
insert @customer values ('John', 'B');
select * from @customer;

Result:

first_name   last_name   full_name
John         B           John B

Upvotes: 2

Related Questions