Tables/Normalization/Relationships?

Ok, with all of the problems I've been having with my database, I think I've finally gotten to the nitty gritty of it - that I need to normalize it and have more tables! This was the only thing I was NOT looking at in trying to find the answer! I've been reading all day about Normalization and understand the concepts. I'm just having a hard time relating it to what I need! So I thought, maybe, if I told all of you GURUs what FIELDS I have (right now all in one table, need to be on ONE report is all) and what kinds of Fields they are, maybe you can help me figure out how to normalize it all!??!?!?!?! I also have to try to be careful here so as not to give out any specific company information! )

Ok, here is a listing of ALL FIELDS and what kind they are...then I will explain a little more.

COURSE # Text/5 Like "?????"
DATE REVISED Date ??/??/??
REVISION LETTER Text/3 Default "NEW"
COURSE TITLE Text/50
PART # Text/7
MODEL # Text/50 Default "N/A"
CREDIT HOURS Text/50 (can be ?? minutes/hours/days)
DATE CREATED Date ??/??/??
MEDIA USED Text/50
REQUAL/TRAINING CYLE Text/15 Default "NONE"
COURSE GOAL Text/150
PREFERRED AUDIENCE Text/100
COURSE PREREQUISITE Text/50 Default "NONE"
TEST REQUIREMENTS Text/65 Default "100%"
ATTENDANCE REQUIREMENTS Text/50 Default "100%"
REVISION COURSE PLAN Text/250
REVISION SUPPORT MATERIAL Text/150
PROGRAM DEVELOPER Text/50
MGR, TRAINING Text/50
REQUIRED STUDENT MATERIAL Text/75
RSM P# Hyperlink to PDF document
(actually have 7 of the above two items, like RSM1, PRSM1, RSM2, PRSM2, etc.)
OPTIONAL COURSE MATERIAL
OCM P# (same as Required Student Material)
REQUIRED INSTRUCTOR MATERIAL
RIM P # (same as Required Student Material)

That's all on PAGE 1 of my report. I'm not even getting into Page 2 here because it's a complete NIGHTMARE! I figure I'll see if anyone can help me with this before I throw that at you! )

Ok, so basically: The COURSE # is a UNIQUE #...HOWEVER, when we input the next REVISION, it will have the same COURSE #, with a different REVISION LETTER, so I'm thinking the TWO of those TOGETHER can be a combined KEY. (???? agree ????)

All of the MATERIAL fields (with P#s) are iffy. There can be ZERO, ONE, or SEVERAL, for every course #, it will be different for each one. So in reading about normalization, that's really throwing me off!!!!! Because it says basically if the field is blank/null at least 50% of the time, it should probably be it's own table. ?!?!?!?!?!

Also, REVISION COURSE PLAN and REVISION COURSE MATERIAL are both fields that can be blank too. So I know they need to go with the REVISION LETTER and REVISION DATE. HOWEVER, NONE of those are unique fields. I guess I would need to put the COURSE # with it and again, make both the COURSE # and REVISION LETTER a combined key? I'm so confused!!! (Imagine, I'm alot less confused though than I was before finding out I needed normalization! whoa, scary! lol)

I've got an email into my boss now asking if the P#s are going to be completely unique, that should help answer one question about them. I *THINK* they will be, but not sure.

Ok. That's all my questions RIGHT NOW about this PARTICULAR problem (have others! lol). I hope you can see why I'm confused yet know exactly how to fix me!!!

As you can see (I hope), it's not exactly like all of the examples used in explaining Normalization...it's not like I have COURSE INFO, and STUDENT INFO, and ORDER INFO, and obvious separate categories to make into tables. Everything on here goes to a COURSE only!!!

I sure hope someone out there can help! HAVE A GREAT WEEKEND!

Kris

P# answer

Hi. I said in my last post that "I've got an email into my boss now asking if the P#s are going to be completely unique, that should help answer one question about them. I *THINK* they will be, but not sure. "

Well, got the answer. The P # CAN be on more than one course.

Just wanted to add that new piece of information (and of course it's NOT what I thought it was! lol)

Kris

# of Materials fields

Hey guys, me again. I just wanted to update this again, with new info (sorta). I need to be able to have up to 10 spots each for all the different MATERIALS (Required Student, Optional Course, and Required Instrctor). 10 EACH.

So it can be anywhere from ZERO to TEN for each category.

Just in case this info helps AT ALL.

Kris

It is difficult to normalise only on a list of fields. Have a crack at it yourself by examining your data and sorting it into natural groupings as you only know what is related to what. eg course details ie Course#,CourseTitle, CourseGoals etc.

Post your attempt in the following format and how the data should relate.

TableName
--------------
Field1 (usually primary key and usually autonumber)
Field2
Field3
etc

Table2
---------
etc...

(Pat, feel free to pull me apart here!)

edit: seems like I'm late to the party here - I will have to read the previous topics to bring me up to speed *at least Pat did not correct me - phew*

krishill, I'm glad you are taking my advice. Start by grouping the fields as they are grouped on the report. Assume for starters that each section will be a separate table. Once you start building tables, it is best to use proper naming standards for forming the table and column names. Basically, eliminate all embedded spaces, don't use special characters such as "@#$%^&*<>;;/, etc.", start all names with a letter and use only lower or upper case letters, numbers, and the underscore _.

unique keys?

Hi Pat! Thanks for the reply. What about the fact though that NOTHING on here is a unique value except the Course#...and get this! I just found that we're doing away with all the #s because our new system can't do them so they'll just be the course titles! So the course title will be unique I guess (like the Course#, combined with Revision letter).

I know I read that if there isn't any primary key/unique value for a table, to make one, like an autonumber field. I guess that's what I need to do? And just not put that field anywhere on the form and report? Did I actually answer one of my questions CORRECTLY? lol, go ahead, tell me I'm wrong! lol

Well, I'm outta here for today, be back tomorrow God willing!

Kris

relationships

Hey guys, working hard on this. My last question I still need answered, but have another one...this should be easy. Do ALL tables in a database have to be either one-to-many or many-to-many (or one-to-one) or can I have two tables with a one-to-many relationship but another table with a many-to-many relationship? Did that make sense? Hope so!

Kris

The 2 basic joins in tables are either one-to-one or one-to many. Each table can have as many joins to as many other tables in either way. you cannot produce a many-to-many with only 2 tables, you need a junction table. Pat has posted an example here of many-to-many relationships.

One table can have a one-to-one with one table, a one-to-many with another table and a many-to-many with another table (via a junction table) within the same Db.

How can you help?

Hey guys,

Ok....how can I lay everything out so you can help me normalize and link all my tables? Just give you the table names & fields for each in the format Fizzio gave me above? Would you be able to help just with that info or do I need to give you anything else? Before I typed everything out, I first wanted to find out exactly what you guys would need!

Thanks bunches!

Kris

It is probably easiest if you actually create the tables in a new db (remember the proper naming standards) and then connect the tables in the relationships window. You can then post a picture of the relationships window or the new db.

ALL tables should have a primary key. If none of your data fields stand out, use autonumbers. Name them after the table name. So for example, tblCourse will have a pk of CourseID (autonumber); tblCourseRefMatl will have a pk of CourseRefMatlID (autonumber) and a foreign key of CourseID linked to the tblCourse and a foreign key of RefMatlID linked to the tblRefMatl. tblCourseRefMatl is a junction table. It is used to relate many courses with many reference materials.

The course table has a natural key which is composed of two columns CourseNum + CourseRev. I would define this as a unique index, which will satisfy the business rule againse duplication, but not use it as the pk.

Relationships ?????

Ok, please let me know if you cannot see this correctly for whatever reason (I just saved it as a Winzip file?). You'll see that I have no idea how to link my MATERIALS table to anything else. The MATERIALNUMBER field will be the same as the P???? fields in the three other tables...do they have to have the same name? Because there can be anywhere from ZERO to TEN P#s for each course, for each category (made each category a table).

PPPPLLLEEEEEEAAASE let me know what I can do here! Wow and this is just setting up the table...when I thought my report was going to be the hard thing! I guess if the tables work right, then the report will be cake huh? lol

Please let me know what you think. Have a great weekend! Thank you so much for all this help!!!!!! You have no idea how much it's appreciated!!!

Kris

I made some very quick changes to give you a start. I created the proper relationships and built part of your main form with three subforms so you can see how the many-to-many relationships work in your case.

The reason that each set of data requires its own table is that each set has a many-to-one relationship with course but NO relationship to each other. For example, student course materials have no relationship to instructor cours materials but they both have a relationship with course. I think you'll understand the whole thing a little better when you see it set up for your particular situation.

I'd like to enrol in "Pat's Access Course" please

If I get to visit your lovely country again, I'll sign you up I fell in love with Sydney and would visit again in a heartbeat except that it takes so long to get there from here.