Splitting Name Table in database design

Question

I have a database with 5 millions records, i have Person table having 5 million records and its have 13 columns. Forename and surname column is heavily populated as compared to other columns. My question is it appropriate to separate forename and surname tables with relevant ID’s. Will it reduced the load on database or What would be the benefits/disadvantages of this approach?

Structure of my table(HCP) is

HCP-ID  (PK)
Title-ID  (FK)
Surname
Forename
Forename-Initial
Specialty-Code
Specialty
Gender
Eligibility-ID  (FK)
Loyalty-ID  (FK)
Designation-ID  (FK)
HCP-Language-ID
Source-ID  (FK)
Updated-By-Staff-ID (linked to User-ID)  (FK)
Update-Date
Stored-Payment-Details (y/n)
Contact-Mobile-Number-ID

possible duplicate of [Why use a 1-to-1 relationship in database design?](http://stackoverflow.com/questions/9687980/why-use-a-1-to-1-relationship-in-database-design) — Branko Dimitrijevic, Dec 15 '13 at 11:42
Please add more information. It's not clear what you want to do. — Bogdan Sahlean, Dec 15 '13 at 11:44
Please provide the actual table structure + table structure you think at. Then specify why would you need to do this. — bjnr, Dec 15 '13 at 11:49
It *seems* you are talking about normalising your data, but that is not at all clear. The table structure as well as a sample representing your data would likely be very helpful. — Andriy M, Dec 15 '13 at 11:55

M.Ali · Answer 1 · 2013-12-15T13:49:09.130

I have some experience of working with Healthcare Providers database and looking at this table I think you can do the following.

Main HCP Table

HCP-ID  (PK)         
Title-ID  (FK)      --<-- every HCP will have a title
Surname             --<-- every HCP will have a Surname
Forename            --<-- every HCP will have a Forname
Forename-Initial    --<-- You dont need this column as you have this info stored in 
                       -- Forename column all you need to do is use LEFT(Forename, 1) 
Specialty-Code     --<-- One HCP could be responsible for multiple Specialities therefore
                      -- I would move this column to another table.
Gender             --<--  Keep this Every HCP will have some Gender 
HCP-Language-ID    --<-- Every HCP will have a Language ID (Mother Tongue )
Updated-By-Staff-ID --<-- required to Audit Table who has updated the record.
Update-Date         --<-- required to Audit Table when was the record updated.

Now for the rest of the columns you can put them In a separate table and Reference records back to your main table using a FK or put them in multiple separate table and use Fks.

HCP Details

Eligibility-ID  (FK)
Loyalty-ID  (FK)
Designation-ID  (FK)
Source-ID  (FK)
Stored-Payment-Details (y/n)
Update-Date
Updated-By-Staff-ID
Contact-Mobile-Number-ID
Specialty          --<-- When you have Specialty-Code why store Specialty Name ???
                      -- use JOIN to retrieve the info when ever needed.

Splitting Name Table in database design

1 Answers1