Database design lots of tables or a few big ones?

Will · May 27th, 2009, 04:29 AM

Well I will definatly look into it then if it will save me so much pain and god forbid self mutilation

.

I only wanted to go with what I already had becuase I have to have this finished by tomorrow and it seemed as you said abit daunting to try and learn a new concept.

Will · May 27th, 2009, 05:20 AM

Ok I read through the articles and I think the objective is to not have data in a table that is repeating itself or not depending on the primary key, atomicty and uniqueness it was referred to.

I definatly understand and appreciate the benefits of this, but I am unclear on how to implement it myself. Presumably I start with one big table which has all my data in. I then systematically take it apart? Find out what uniquely identifies each group and make a concatenated PK, then I need to remove partial dependancies on the concatenated primary key and move them into new tables, I then need to keep repeating the process until all tables pass NF2.

Lastly I need to remove dependancies on non-key attributes. But ensure there are still relationships between the tables that need them.

This is a really simple and yet hard concept to understand at the same time.

gbianchi · May 27th, 2009, 07:54 AM

Let's try a little example.. Could be hard the first time, but once you understand the concept and how to achieve it is very simple... ( I take only two of your tables that explain the whole concept) And take in mind that maybe the proccess is simplified, long time doing this...

You start with a table like this:
Table Name: Children
IDChild (I prefer Child ID): Primary Key
Carer :
Last Name:
First Name:
Date Of Birth:
Address:
PostCode:
Religion:
First Name No pick up people:
Last Name No pick up people:
Relationship To child No pick up people:

Then you define the KEY (in this case you already have and ID, But in RL You never have the ID, but that the first Field you add ;) ). The Key are the least number of fields that uniquely identify the row. In this case, First name, last name, date of birth (in case you have 2 childrens with same name), address (In case the 2 children have born the same day). This could look weird, but gave you enough data to find a unique row (Twins share the address and date of birth, but probably not the name, but you never know).

Take Out repetitive groups (and add a FK):
NO PICK UP TABLE
IDChild:
First Name No pick up people:
Last Name No pick up people:
Relationship To child No pick up people:

(Repeat until no more repetitive groups)
PEOPLE'S NAME (FROM NO PICK UP, because some brother could share this info ;) )
NAME NO PICK UP
IdNoPickUpName:
First Name No pick up people:
Last Name No pick up people:

RELATION (FROM ANYWHERE YOU NEED A RELATION):
IdRelation:
RelationName:

NO PICK UP TABLE (Final)
IdChild:
IdNoPickUpName:
IdRelation:

Master Table (need more work)
IDChild (I prefer Child ID): Primary Key
Carer :
Last Name:
First Name:
Date Of Birth:
Address:
PostCode:
Religion:
Carer, address, Religion should have separate tables. (With Id, and description)

once you take that out too, you left the master table with this:
IDChild (I prefer Child ID): Primary Key
IdCarer :
Last Name:
First Name:
Date Of Birth:
IdAddress:
PostCode:
IdReligion:

Will · May 27th, 2009, 08:09 AM

So I think I have completed the first step: here is what I have so far in my normalised database:

TableName: Carer

CarerID: (PK)
CarerFName: x
CarerLName: x
CarerRelChild: ?
CarerHomePhone: x
CarerMobilePhone: x

ChildId: (PK)
ChildFName: x
ChildLName: x
ChildAddress: x
Postcode: x
DateOfBirth: x
Religion: x
Legal Guardian: ?
ChildDisability: x
ChildMobilityImpairment: x
ChildMedication: x
ChildPersonalCare: x
ChildOtherInfo: x
ChildPhotograph: x
ChildFacePaint: x
ChildSunCream: x
ChildFirstAid: x
ChildEmergencyMedicalTreatment: x
ChildOffsite: x
ChildSpecialNeeds: x
EmergencyConId: ?
EmergencyConFName: ?
EmergencyConLName: ?
EmergencyConRelChild: ?
EmergencyConHomePhone: ?
EmergencyConMob: ?
DocFName: x
DocLName: x
DocAddress: x
DocTelephone: x
PermId: ?
PermFName: ?
PermLName: ?
PermRelChild: ?
NotPermId: ?
NotPermFName: ?
NotPermLName: ?
NotPermRelChild: ?

The "x" represents columns which don't depend on the concatenated primary key and the "?" represents columns which don't rely on either primary key although I am unsure if some of them should be crosses.

have I got the right Idea so far? Everything in the database can be uniquely identified by the childId and CarerID so that is why I made them primary keys.

Will · May 27th, 2009, 08:12 AM

Thanks for the example, you posted while I was typing mine out. I am reading through yours now but it isn't looking the same as mine lol.

Jeff Moden · May 27th, 2009, 10:19 AM

Quote:

Originally Posted by Will

Ok I read through the articles and I think the objective is to not have data in a table that is repeating itself or not depending on the primary key, atomicty and uniqueness it was referred to.

I definatly understand and appreciate the benefits of this, but I am unclear on how to implement it myself. Presumably I start with one big table which has all my data in. I then systematically take it apart? Find out what uniquely identifies each group and make a concatenated PK, then I need to remove partial dependancies on the concatenated primary key and move them into new tables, I then need to keep repeating the process until all tables pass NF2.

Lastly I need to remove dependancies on non-key attributes. But ensure there are still relationships between the tables that need them.

This is a really simple and yet hard concept to understand at the same time.

Like gbianchi did in his/her example... take that big table apart. And, no... normalization does not mean putting stuff in one wide table. You were closer when you started this thread but need to take it a bit further.

If you really did lookup "Normalization" in Books Online, then you have a really good idea on how to do things. The last paragraph they gave is along the lines of what I was talking about... I've quoted it here, just in case....

Quote:

A table should not have repeating values or columns.

The table for an item in the database should not contain a list of values for a specific piece of information. For example, a product in the AdventureWorks database might be purchased from multiple vendors. If there is a column in the Production.Product table for the name of the vendor, this creates a problem. One solution is to store the name of all vendors in the column. However, this makes it difficult to show a list of the individual vendors. Another solution is to change the structure of the table to add another column for the name of the second vendor. However, this allows for only two vendors. Additionally, another column must be added if a book has three vendors.

If you find that you have to store a list of values in a single column, or if you have multiple columns for a single piece of data, such as TelephoneNumber1, and TelephoneNumber2, you should consider putting the duplicated data in another table with a link back to the primary table. The AdventureWorks database has a Production.Product table for product information, a Purchasing.Vendor table for vendor information, and a third table, Purchasing.ProductVendor. This third table stores only the ID values for the products and the IDs of the vendors of the products. This design allows for any number of vendors for a product without modifying the definition of the tables, and without allocating unused storage space for products with a single vendor.

Will · May 27th, 2009, 10:22 AM

Ok so here it is so far I think I am on the right track..

TableName: Carers
CarerId (PK)
ChildId (Pk)
CarerFName
CarerLName
CarerRelChild
CarerHomePhone
CarerMobilePhone

Link to Carers_Child

TableName: Carers_Child
CarerId (PK)
ChildId (PK)

Foreign Key to Child

TableName: Child
ChildId (PK)
FName
LName
Address
DOB
Postcode
Religion
Disabilty
MobilityImpairment
Medication
PersonalCare
Photograph
FacePaint
Suncream
FirstAid
EmergencyMedicalTreatment
OffSite
Other information

Links to: Child_EmergencyContact, Child_Doctor, Child_Permitted, Child_NotPermeitted.

TableName: Child_EmergencyContact
ChildId (PK)
EmergencyConId (PK)
EmergencyConFName
EmergencyConLName
EmergConRelChild
EmergConHomePhone
EmergConMob

TableName: Child_Doctor
ChildId (PK)
DoctorId (PK)
DoctorFName
DoctorLName
DoctorAdd
DoctorTel

TableName: Child_Permitted
ChildId (PK)
PermId (PK)
PermFName
PermLName
PermRelChild

TableName: Child_NotPermitted
ChildId (PK)
NotPermId (PK)
NotPermFName
NotPermLName
NotPermRelChild

So thats it so far is it looking correct? Still need to apply NF3 I think..

rstelma · May 27th, 2009, 12:53 PM

Hi Jeff,

I didn't mean to say that you suggested a column with the name as only 'ID'. I thought that was Will but it must have been thinking of another post. Anyway, you're right I have never seen a failure in a query that had a column 'ID'.

Yes, OK, I see where you are going with the mapping table now. I was not thinking of business contacts who may work out of the same office. It even makes sense for family members who could live at the same address. I'm not sure how you would do an address look up and address assignment though programmatically.

To quote myself, "I usually use a word from the table name like ContactID", so it seems like we agree on this point.

Thanks Jeff.

Richard

gbianchi · May 27th, 2009, 01:08 PM

Yes.. you still need normalization on this work...

By the way.. Don't knowing the data, looks like the "Master" table are carer and child, who I supposed is having a lot of kind to care to.. so this table:

TableName: Carers_Child
CarerId (PK)
ChildId (PK)

Is not needed, Unless a child can have more than one carer...

And you still need to strip this tables:
TableName: Child_EmergencyContact
ChildId (PK)
EmergencyConId (PK)

TableName: Child_Doctor
ChildId (PK)
DoctorId (PK)

TableName: Child_Permitted
ChildId (PK)
PermId (PK)

TableName: Child_NotPermitted
ChildId (PK)
NotPermId (PK)

To this way, because several children can have the same doctor ;)

Also.. I think childId on the carer table (if that is the final table) should be FK..

Will · May 29th, 2009, 05:54 AM

Ok so here is what I think is the finished database.

TableName: Carers
CarerID (PK)
ChildId (FK)
LegalGuardian
FirstName
LastName
RelationshipToChild
HomePhone
MobilePhone

links to Child table

TableName: Child
ChildId (PK)
FirstName
LastName
Address
Postcode
D.O.B
Religion
Disability
Mobility Impairment
Medication
Personal Care
Photograph
FacePaint
Centre
OtherInfo

Links to: Child_EmergencyContact, Child_Doctor, Child_Permitted, Child_NotPErmitted, Child_Centre

TableName: Child_EmergencyCOntact
ChildId (PK)
EmergencyContactId (FK?/PK)

Links to: EmergencyContact
EmergencyContactId (PK)
FirstName
LastName
RelationshipToChild
HomePhone
MobilePhone

TableNAme: Child_Doctor
ChildId (PK)
DoctorId (FK?/PK)

Links to: Doctor

TableName: Doctor
DoctorID (PK)
FirstNAme
LAstNAme
Address
Telephone

TableName:Child_Permitted
ChildID (PK)
PermID (FK?/PK)

Links To: PErmitted

TableNAme: PErmitted
PermId (PK)
FirstNAme
LAstNAme
RelationshipToChild

TableNAme: Child_NotPErmitted
ChildId (PK)
NotPErmID (FK?/PK)

Links to: NotPErmitted
NotPErmId
FirstNAme
LastName
RelationshipToChild

TableNAme: Child_Centre
ChildID (PK)
CentreId (FK?/PK)

Links To: Centre

TableName: Centre
CentreId (PK)
CentreName

And thats it. Firstly Thank you to everyone who has helped me, secondly I hope I have got this right. Lastly On the tables prefixed with "Child_" should that be a (PK) and then a (FK).

In reading it said that it should be a concatenated primary key, however I was under the impression that you could only have 1 primary key per table and now I am unsure if infact you can have a concatenated primary key or if it is just a concept to help with the normalisation process?