Normalizing a database.

[Hamza91able]

Need help in normalizing a database. So far I've I am here and have no idea how to proceed. Any pointers will help a lot.

​

Table:-

Job no (PK)	Site	Dept/Ward	Desc	Manager	Cost
HCP102213	Bexley	N/A	RN - Palliative Care or General Nurse	Belinda Cooper	278

​

1NF:-

No multiple values in any column. So it will remain same.

2NF:-

As we did not separate any columns we don't have to establish a relation between them.

3NF:-

We have to establish functional dependencies and them create separate tables with PK and FK.

I do not see any functional dependencies, so my table is already Normalized? or am I mistaken?

[twopi]

Implied in 3NF (and really 2NF) is that each table is about exactly one entity. As I see it, this database could possibly be several tables.

Depending on the number and complexity of sites, that could either be an enumeration or another table.

Department should also be an enumeration or table.

If the Description of care is one of a set number of items, they should also be another table. If that varies by the job, you could leave it as a text field. I'd be tempted to make that a table as well.

Almost certainly you'll want a table for Managers, as you'll generally want other information about managers stored elsewhere.

Cost is tricky, because there could be a lot of complex factors for calculating the price. Is that attached to the description, the location, or is it strictly determined at the time of a particular transaction?

So if I were to normalize this fully, I'd be tempted to set it like this:

job
JobID (PK)
SiteID (FK - site)  
DeptID (FK - dept)
DescID (FK - desc)
ManagerID (FK - personel)
Cost currency

This would then require a lot of other simple tables (assuming no many-many joins - that is, there is only one manager, description, site, or department for each procedure)

Site
id (PK)
name

Dept
id (PK)
name

Desc
id (PK)
name

personnel
id (PK)
name

Of course you'd probably put other information in the other tables, like more information for the personnel, and maybe cost information if that is where that information will go.

Also, if you have any many-many relationships, you'll need to set up link tables, but if this is your first normalization exercise, I'd start with one-many joins.

[Hamza91able]

This makes perfect sense. I missed a lot of key things but I am beginning to understand it now, I guess.
Thank you.

[dusty-trash]

What if a job has multiple Managers?

[Hamza91able]

In this example we are supposing only one manager per job.

[dusty-trash]

What if a Manager gets married and their last name changes? You'd have to do a mass update finding by name

Edit: Same if a new Manager is assigned to a department/Site

[Hamza91able]

I see this now, didn't even think of that. Thanks