1

In my Mysql database, I have a Person table which has a key called FirstName.

If there are 10000s of entries, sometimes there are repeats of common FirstNames of people such as John or Bob etc.

How can I edit the Person table to store the repeats only once so that I can utilize memory space and be more efficient at storing data?

User1204501
  • 781
  • 3
  • 14
  • 26

2 Answers2

2

You are looking for an extreme form of normalisation that I would classify as micro-optimisation. Most databases I know of do not go that far, but why not after all.

This can be simply achieved by creating a firstname table that holds all known first names in your database. Then replace your firstname column in your main table by a foreign key reference to this table (typically, a 4-byte INT).

the problem with this appraoch is that you will also create an index on firstname, which will look something like this:

CREATE TABLE fistnames (
    id INT PRIMARY KEY,
    firstname VARCHAR(250)
) ;

The footprint of this table would be 4 (firstnames.id column) + [length of firstname] + [size of primary key index entry]. Also add another 4 bytes for the foreign key in your main table. This is possibly less efficient, storage-wise, than your current structure, especially for those short names such as "Joe".

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • that sounds just like what I wanted. Can you put some code up for this example please? – User1204501 Aug 14 '13 at 09:46
  • What about how to implement the condition on how to check for duplicates etc? – User1204501 Aug 14 '13 at 09:51
  • There are [loads of examples out there](http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html). Search for "foreign key" or "one to many relationship". To prevent duplicates, add a [unique constraint](http://stackoverflow.com/a/10908676/1446005). – RandomSeed Aug 14 '13 at 09:52
1

instead of having key on FirstName, you should have id as key. Also remove any key from FirstName column.

you should think of in terms of memory versus functionality and speed. If you want to save memory by having a key on FirstName column, you will have to sacrifice your functionality of having multiple persons having same name.

You should add an id column with primary key on it in person table.

As per your request, I am giving you template of person table

CREATE TABLE persons
(id int auto_increment primary key, 
FirstName varchar(40),
LastName varchar(40),
details varchar(100),
...any other columns
)

if you do not have the id field before then use

ALTER TABLE persons ADD id int auto_increment primary key;
Krishna Rani Sahoo
  • 1,539
  • 1
  • 14
  • 25
  • what if I wanted to have the functionality of having multiple persons having the same name without worrying about memory space? – User1204501 Aug 14 '13 at 09:45
  • just remove the key from `FirstName` and if you have `id` column then make it `primary key`. if no `id` column exists then add it – Krishna Rani Sahoo Aug 14 '13 at 09:48