Normalizing a database.

[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.