Does this Table design make sense

[vp7799]

I posted a question and someone questioned my table design, he had good cause this is the 1st large database I have ever done. Here is a quick example of what i have.

Table 1. Personal Table
This table includes Social Security Number, Last Name, First Name, stuff like that
Table 2. Millitary Info, (I am in the Army)
This has stuff like Rank, Job, Squad and section
Table 3. Security Info
This has table has info on if they have a security clearance
Table 4 EPSQ Info
This table has information on if they have applied for a clearance.
Table 5 Security Verification
This Table lists the 4 ways I have to check security clearances.
I have this many tables becase I thought it was best to have small tables, plus not every one is going to have a securty clearance. There are more tables but this should give you the idea. Plue my primary key is the social securty number. Am I on the right track, or could improvements be made. Thanks

 

[KenReay]

Hi

If the relationship between all the tables you listed and the 'Main' table is one - one, then why not simply combine them all into one table, was my point, but without knowing the relationship type between the tables it is difficult to be sure.

Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[vp7799]

As I said earlier the reason i broke it up was so i would not have a massive table. Which is what it would be. Is it ok to have a massive table? Thanks for the help eariler, someone gave me a simple solution, change my join properties in the queries i was doing. Thanks yet again

 

[mymou]

Hi vp7799

The size of table (ie number of fields) is almost irrelevant if they are designed carefully.

Anything that can be contained in one row of data - probably should go into one table.

So for each Social Security Number - each entry has only one name, one rank. So Personal Table and Millitary Info should be one table for sure. Security Info and EPSQ Info could also be included in this table - but more details would be required to confirm this.

I kind of see:
tblPersonnel
Social Security Number, Last Name, First Name, Rank, Job, Squad, Section, Applied for clearance1, Applied for clearance2 etc, Cleared1, Cleared2 etc.

This would be fine if the clearance issue is simple (a checkbox or a date). If however, you need lots of fields(application date, Officer in charge of application - i dont know)- it can still go into one table - although it would be slightly more efficient in a separate table (lots of empty fields for lots of people) - but this isn't a big issue. If however there may be more categories in the future - then an additional table is essential.

Think that is it. Think about indexes after that.

Stew

 

[vp7799]

Hey,

Once again thanks, If I was not so far along in the database, I would go back and redo. The only problem with the database is the table setup. I dont forsee any problems in the future, but next time will not be afraid of big tables. Thanks