Single Table?

[aircuart]

Hi,
I asked a question in the Query section and mentioned that I had made a table with "the usual last name, first name, course1, course2 etc" The Query reply was very helpful but one of the replies said I should go look at the Normalisation post. I have indeed been and looked and have a question.
My database is basically a tracker of skill sets an individual has (or will have), along with some data on that individual. There is a record set for each individual. I work in a specialised environment and I am interested in knowing the skill sets of an individual(or group) when those skills are due to expire or when they are booked for. I can produce numbers and statistics relating to individual or group skill sets. For instance certain tasks require that an individual is fully qualified in 3 Maritime skills to operate in a Maritime environment. The date that the individual qualified in those skills is stored in the respective field. I do the calculations (to ensure qualification is in date still) on a report and produce a name (or names) of those qualified personnel.
So in my case doesn't a single Table work better for me? I am not a stock control or customer based design so many of the sample databases I have seen don't fit my requirements. I still feel though, having read many of the Table posts that I am committing a major sin with my design.
I am very new to access and the table was cobbled together with an "Access 2003 for Dummies" (quite apt in my case ) in one hand and a rough requirement drawn out in the other. I would like the database to be user friendly, efficient and structured for future use, so if a re-design is necessary then so be it. The data is in it and can be exported so a complete rebuild is not out of the question. Any advice, criticism or smacking with big "don't do that" stick will be much appreciated.

 

[plog]

So in my case doesn't a single Table work better for me?...I would like the database to be user friendly, efficient and structured for future use,

Nope, a proper structure is the way to go. From what you've described you need at least 3 tables (People, Qualifications & Skills).

Coming from Excel, which it smells like you have, its easy to get in the one table is all I need mindset. My advice is to give this normalization thing a shot. Your data isn't that complex, so structure your tables like we advise, then move onto queries to see if you can get the data out that you want. Then you move onto generating reports and making forms.

 

[spikepl]

tblPeople
----------
PersonID (PK)
PersonName
PersonBlaah

tblSkills
--------
SkillID (PK)
SkillName
SkillBlaaah

tblPeopleSkills
---------------
SKillPersonID (PK, autonumber)
SkillID (FK)
PersonID (FK)
ObtainedDate
ExpiredDate
etc ...

 

[aircuart]

@ Plog
First of all Thank You for your reply, and yes I generally work in Excel I shall read more and maybe post how it I think it should look for constructive (or destructive) criticism.

@ Spikepl
Thank you for your reply as well, and, as above I shall work on a structure and throw it up for further guidance.

 

[plog]

Yes, give it a shot and post back here. The best method is to go to the Relationships screen and take a screenshot with all your tables in it.

 

[aircuart]

Hi,
I have attached a screen shot of the Tables I think should exist. Where I think the relationship is correct I have connected the Tables. Where I am having a bit of difficulty, is in how to relate tables, with Qualifications (ie MTQual, AirQual etc) to a Table such as Spikepl suggested.

tblPeopleSkills
---------------
SKillPersonID (PK, autonumber)
SkillID (FK)
PersonID (FK)
ObtainedDate
ExpiredDate
etc ...

I think he meant to have one table with all the qualifications in but when i did that it was one large table and a bit messy. He also suggested doing the dates in this Table if I understand correctly. Most of the Qual Tables you see have Date/Time as the Data Type. I plan on doing the calculations for due date on the Form or Report as there are several factors influencing some of these dates. Any advice or direction would be much appreciated, again apologies for my Ineptitude

 

[spikepl]

Yes he did mean to have one table with the skills! Correction: qualifications or PeopleSkills

Similar stuff always goes into ONE container. You do not encode data into the structure. At home do you have a kitty for milk, one for newspaper, one for pizzas, one for beer, one for whatever? And no clue how much you actually have? Or do you have one pot with money to be spent as required?

You have hardcoded information into your structure, and any change /expansion would require changin structure, which is a huge no -go sign! And further, eternal PITA because it is so hard to make a query or report and to maintain it.

Back to the drawingboard amigo Read more about normalization.

 

[aircuart]

Spikepl thank you for your reply and apologies for the way I answered. I was replying, initially to Plog, but should have replied to both of you. I have placed all the Qualifications into one table as you suggested and I have watched several very good Youtube presentations on Normalisation. Thought I had made some headway but am thinking I may need more nudges in the right direction. Most of my Data is Date or Y/N sort of Data the exception being the personnel related stuff. Where it starts to hurt my head is trying to eliminate replication. For instance the field PersonnelTrade. Personnel can be ATM,AV or AMM (there are more but for brevity these will do). It just seems easy to have a list value on a form and place the appropriate Trade into the table as opposed to having a table with the Trades in it? Is this what you mean by having hard coded information? I have attached an amended relationship view for more direction and I shall go read (and watch) more about the correct set up for Tables. Thank you again for spending the time, in trying to make me understand and I shall try my best to see the normalisation light!

 

[plog]

You've made a lot of the same mistake--storing information in field names. Here's all the table you have that issue with:

TblPriAdd - All the number fields shouldn't exist, instead for everyone you need, you add a new row. If the number is relevant (which I'm not sure it is) you create a field for it to store it.

TblNOKAdd - All numbered fileds shouldn't exist, same as above.

TblGold - All those lettered fields shouldn't exist similar to how the numbered fields of the other tables shouldn't.

TblQualification - I'm pretty sure this is in the same situation. Each type of qualification shouldn't be part ofa field name. Instead you have a QualType field which holds the information about which qualfication it was for.

All of those tables have the same error--storing data in field names. To demonstrate how they should be let's use this table as an example:

tblStudentGrades
StudentID
Grade_Math
Grade_English
Grade_History
Grade_Chemistry
Grade_Biology


That's an incorrect structure because relevant information (course type) is being stored in the name. This is what the proper structure is:

tblStudentGrades
StudentID
Course
Grade

That's it, just those 3 fields. Now, that means that table will have a lot more rows than the incorrectly structured one (a row for every course, instead of 1 row per student), but that's how database are suppose to work. Tables should grow vertically (with more rows) and not horizontally (by adding more columns). A benefit is immediately obvious too--adding a new course is simple, just add the data. If we went with the initial structre to add a new course we'd have to add a new column, which means we'd have to add it to any form, query or report we used that table in.

That's your issue in all the tables I listed, and how you should fix those issues. I'm certain you have more issues, but I'd need to see some sample data to identify those. For now, make those revisions and post back here your revised structure.

 

[aircuart]

Think I have finally grasped what you mean...hopefully. My problem, other than stupidity , was that in my head I saw a spreadsheet with names running down the left , courses etc across the top and data in the cells.
The Add1,2,3,4 in both the Pri and NOK tables are where I have broken the Address into 4 parts. Personnel can be in a Room in a Block or a House in a Street (various Towns and Counties, Post Codes etc) so I thought it would be better to break it up into easy parts. Should I just have one Field for the entire address?
One other question, in the RAFFT Table I have a Field: RAFFTPassStandard this can be one of several things (Light Blue,Blue,Pass or Fail) should I just have them as a Qualification in my new QualTable (ieQualRAFFTLB,QualRAFFTPass etc)? It gets bit more awkward with the review as that is dependant on being a fail or on a medical category (ie if a fail then 12 week date to retest or if medical then a review date as prescribed by physio). I just think, with your comments in mind, it might be a nugatory table.
I am in the process of re-designing and will post as soon as I can, but thought I would address (no pun intended) the first question. Thank you again for your comments and advice.

 

[spikepl]

We do not know your business and know only what you supply. If you want advice supply complete information and use words recognizable to outsiders, not internal acronyms.

I gather that you have some qualifications that are (inter-?)dependent: to keep the qualification you need to show some proficiency (usage/test/whatever within some past period) and futher, you have in parallel have some specific or unspecific medical. Now to get advice on this you need to provide all conditions pertaining to this.

As to your address question: we have, again, no idea what is in your mind when you say add1, 2,3..4 - street, county, postcode, city? Something else? Don't make us guess - always show examples.

 

[aircuart]

@ AccessBlaster, just read it it and it is one hell of a thread. Quite a lot to take in

 

[aircuart]

My Business is Aircraft Maintenance (Military) and we have a terrible habit of using acronyms known only to ourselves I have attached some examples of the types of data and some notes to go with them. Also attached is V3 of the relationships. I will keep working away at understanding the basic foundations of Tables but hopefully the attachments might make it a bit clearer of what I am trying to achieve. thank you again for your advice and time.

 

[plog]

First let me see some overall issues and then go through your data image by image:

Overall - you should only use alphanumeric characters and underscores in table and field names (no spaces). That means fields like [Last Name] should be LastName. It will just make writing code easier later on. Also, you use some reserved words (http://support.microsoft.com/kb/286335) as field names (e.g. [Number]). This too will cause coding issues later on. Instead you should prefix it with what that number represents (e.g. PersonellNumber).

Address - Each discrete piece of data needs to be in its own field. That means both Address fields should be broken out into their discrete values (a field for House/Street, field for Town, field for County, field for PostCode).

Personnel - GoldLogin should be its own table since not everyone will have it. It would have the Number value from Personell and the GoldLogin value. If no value, they don't get a record in that table.

Qualification & Fitness - looks good, but don't really understand it to comment. Especially since Fitness table isn't in your relationships.

 

[aircuart]

Plog;
I have done the changes as you specified. The Fitness Table is now in the relationship. Like the GOLDLogin, not everyone will be exempt (in fact very few) so I have tied it in the same way. I have included, in the TblPersonnel, everything that is unique to the individual. If you could have and look and let me know what you think that would be much appreciated.

 

[plog]

That looks good. The only question is--does everyone in Personnell get a locker? Can they have more than one?

 

[aircuart]

Nearly everyone has one Main Locker but not a FOD Locker (just a small locker for valuables). You can only have one of each type. Therefore whilst the majority will have one Main and one FOD it is not the case for everybody. Therefore I decided that the Locker Table was the right thing to do? Thank you again for your time.

 

[plog]

Good explanation, your table's are correct for that then.

 

[aircuart]

WooHoo progress I shall start to put some data into it and then probably start asking questions about queries and forms. Incidentally I went back to our "Single Table" effort and while it did a job of sorts, I now realise how bad it is Thank you for starting me on the straight and narrow path!