Database design lots of tables or a few big ones?

Will · May 26th, 2009, 05:39 AM

Is it better to have lots of little tables or one big table?

I have a booking form which has about 50 fields that need to be filled. So I have 2 main tables Child and Carer; but then I have lots of fields which are relevant to the child table like emergency contact, doctor, consent, religion etc.

Would it be better to put them all into the child table or have different tables and then just use a stored procedure to query the relevant details when they are needed?

gbianchi · May 26th, 2009, 07:39 AM

IMHO there is no problem in having one large table. If the data is not repetitive and doesn't deserve to go on another table, there is no need to add Joins to a basic query.

Jeff Moden · May 26th, 2009, 07:50 AM

Depending on it's size, there actually can be a huge problem with having it all in one big one. That problem would be index and statisical maintenance.

If a large table is split into many smaller ones based on date or some such, then maintenance gets easier and so does the archival process if one should become necessary. Think "partitioned table" when you think of very large transactional and history tables.

I recommend you lookup "partitioned table" before you design a single large table or even a bunch of small ones. Of course, you need the "Enterprise" Edition to do partitioned tables. You can, however, make partitioned views even in SQL Server 2005 in the "Standard Edition". They're not quite as easy to maintain, but your maintenance plans will love you.

Will · May 26th, 2009, 08:37 AM

Thank you for both of your responses.

Here is what I am working with at the moment:

Table Name: Children
ID: Primary Key
Carer ID:
Last Name:
First Name:
Date Of Birth:
Address:
PostCode:
Religion:
Legal Guardian:

Table Name: Carer
ID: Primary Key
Last Name:
First Name:
Home Phone:
MobilePhone:
Relationship to Child:

These are the 2 main tables. The next tables information relate to either of those 2 tables. I am not sure if the information would be better off being put into 2 big tables or keep these individual ones.

Table Name: Emergency Contact (each child needs two emergency contacts specific to them)
ID: Primary Key
Last Name:
First Name:
HomePhone:
Mobile Phone:
Relationship to Child:

Table Name: Doctor Details (Each child needs these specific to them)
Id: Primary Key
Last Name:
First Name:
Address:
Telephone:

Table Name: Further Details (Each child needs these specific to them)
Id: Primary Key
Allergies:
Special Needs:
Dietary Needs:
Other:

Table Name: Pick Up People (definatly needs a better name; needs to be specific to the child)
Id: Primary Key
First Name:
Last Name:
Relationship to child:

Table Name: Not Pick Up People (Again better name; needs to be specific to the child)
Id: Primary Key
First Name:
Last Name:
Relationship To child:

Table Name: Disabilities (specific to each child)
Id: Primary Key
Disability:
Imparied Mobility:
Equipment needed:
Medication:
SpecialAttention:

Table Name: Consent (specific to each child)
Id: Primary Key
Photograph:
Face Paint:
Sun Cream:
First Aid:
Emergency Medical Treatment:
Offsite:

So you see I have 2 ideas as to how to go about this.

Idea 1:
Put all the extra child information into the Children table. The problem with this is that sometimes each child will have more than one emergency contact and this way wouldn't allow it unless I put in extra headings like Emrgency Contact 2, 3 etc and allowed nulls.

Idea 2:
Add a Child Id column or something to that affect to each of the extra tables so that each entry is linked to an entry in the children table.
Problems with this idea is I am unsure how to do that..

What way do you think would be the best. I am leaning towards the second idea.

gbianchi · May 26th, 2009, 09:21 AM

mmm.. Why you want to put all that info into the child table?? The current setup is perfectly correct. I think you don't fully understand how a database works. Could be?

The current setup, allows you to avoid having fields with empty data and it's arrange to avoid having repetitive data into one several rows of data.

Why you want to put all of that into one table? could you specify what is your idea behind that?

Will · May 26th, 2009, 09:30 AM

Yes I am very new to databases. This being the second one I have designed.

There was no real reason other than I would find that easier to do as I am not sure how to set it up so that each table has a relationship with a specific child in the children table. Would you have a link or a resource I could through to learn this?

Although I do understand that it solves problems of empty rows etc.

gbianchi · May 26th, 2009, 12:47 PM

Not a specific resource.. but any database book or google will help you.

rstelma · May 26th, 2009, 02:31 PM

Yes, and in particular look at Normalization. That's a very good guideline for database design. I don't know of one pro that does not use Normalization in database design. That will help you determine how many tables you will need for your data.

Will · May 26th, 2009, 02:40 PM

Ok thank you I will check that out? Is it a website? I will google it. Incidently What is the best way to store just a date in a database? I had put datetime but that doesn't work as I am just wanting to store the date. I also just want to store the time in another field. I can't get a datatype for this though?

Jeff Moden · May 26th, 2009, 03:30 PM

Ummm.... I agree... option 2 is the best especially for normalization and flexibility.

As you said, emergency contacts, doctors, parents, and other points of contact could have more than one home phone, more than one mobile, more than one address, etc, etc. I recommend a separate address table with a mapping table between the contact and the address table. Same goes for a phone table.