Normalization - am I doing it correctly?

[sunset1215]

Hi all,

I'd like to know if i'm going in the right direction with normalization of the tables. I've attached the database.

Also, i've got a question regarding importing data into this database. I have 4 tables of data in excel, and one of my excel table is a combination of tblCustomer, tblContact and tblTest. How should i format the data in excel to make the import? I will be missing values for the PKs and FKs isn't it?

Vivian

 

[AccessBlaster]

Just a quick comment. You have three tables tblSam1Pro, tblSam2Pro, tblSam3Pro this is duplicate information so this is outside the norm. Also tblCustomer and tblContacts seems redundant, it’s possible you could have a single customer table or a customer and customer details table. I do like the fact that you haven’t gone any farther then table design kudos to you!

Richard

 

[sunset1215]

EDIT: Ok, i found the reason. i should delete tblProfile, and use fkResID for my 3 subprofile tables instead. i haven't made the changes, but i think it'll work. thanks all.


Hi Richard,

Thanks for the reply. I have worked more on the database since then, and i agree about the contacts part. i'm going to combine them into one table. i've attached the latest version of the DB that i'm working on. it has the original names and all.

i'm having trouble with the profiles. i have a form that contains the respondents' names and contact, and 3 subforms which is their profiles for "pad", "lin" and "tam". i think my relationship here is wrong, as i could not input data in the subforms. it gives me the error 'Join key of table not in recordset'.

i'm actually creating this DB a second time. after reading some threads and articles on normalization, i realise that i'm actually inputting and storing duplicate data with the DB i've built previously. so i'm working on the normalizing it. furthermore, i created lookups at table design. i decided starting all over is better than trying to edit the one i've buit previously.

many thanks for the advice,
Vivian

 

[AccessBlaster]

Hi Vivian,
Normalization can be hard to get your head around. This link explains some of the concepts in basic terms.

http://allenbrowne.com/binary/Access_Basics_Crystal_080220_Chapter_03.pdf

 

[sunset1215]

thanks. i'll be reading up more on access over the weekend, before going further on my database.

 

[rodmc]

good wee article on normalisation
http://db.grussell.org/section008.html

 

[sunset1215]

Hi,

Thanks for the articles. I actually feel that there's more i don't know when i try to learn more.

I have once more made changes to my db just now. (I thought i had finalised the structure, yet i changed it again. =( )

I have a question, that is, is there a way to limit records to just one in a one-to-many relationship? this may seem weird, but i have some tables in a one-to-many relationship. the reason it is so is because there might be no records for some people and while others have it. but the group of people who has the record can only have just one record, so i was wondering if i can lock or unable additions to more than 1 record?

EDIT: I've got another question, is having 3 subforms in a form reasonable? it's the tblTrial, where i can have many projects to one trial, and many codes to a project, and then many pieces of one code.

i just want to know if i should actually combine all into one form? if it's okay to have just 1 form, then i just have to work on the designing of the form, to make it look more neat.


thanks in advance
Vivian