CODEWITHSUNDEEP
sql-serverdatabasedatabase-design
user2558010
user2558010

Reputation: 1

Splitting Name Table in database design

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

Upvotes: 0

Views: 94

Answers (1)

M.Ali
M.Ali

Reputation: 69514

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.

Upvotes: 1

Related Questions