please help me with db, stuck with option group (1 Viewer)

[LarryE]

Occasionally, when building a database for myself, I violate rules like this one and I am ALWAYS sorry. It is no more difficult to separate the name parts than it is to mush them together and once you mush them together, separating the components is a bitch and will always result in data anomalies so forget your Dear Mr. Jones letters or sorting by last name.

That is true. The OP had them together, but I am going to separate them into LastName and FirstName.

 

[TipsyWolf]

That is true. The OP had them together, but I am going to separate them into LastName and FirstName.

then i have to add - we have a middle name as well. its father's name and its very much important for us as name itself

 

[Pat Hartman]

then i have to add - we have a middle name as well. its father's name and its very much important for us as name itself

Make as many discrete fields as you need. Typically, I use:
Prefix (Mr, Dr, Mrs, etc)
FName
MName
LName
Suffix (Jr, Sr, Exq, etc)

This gives you all the flexibility you need to make nicely addressed snail mail and letters as well as providing sorting ability.

 

[LarryE]

then i have to add - we have a middle name as well. its father's name and its very much important for us as name itself

OK

 

[LarryE]

1. How many participants do you normally have for each training session over 2 days?
2. How many rating questions do you have for each participant?
3. How many test questions do you have for each participant to complete for each training session?

The reason I am asking is because this database could become enormous over time because for each training session, each participant will complete numerous test questions and numerous rating questions. I just wanted to warn you. That is also why it may take a long time to actually get a working model for you to test. If you multiply the number of training sessions you conduct over time X the number of participants you normally have X the number of test questions + the number of rating questions, you can see the number of records in the file could get enormous.

 

[TipsyWolf]

How many participants do you normally have for each training session over 2 days?

its 5-20 usually. per trainer. each trainer might have 1-4 training sessions per month. there are 10-15 trainers.

How many rating questions do you have for each participant?

9

How many test questions do you have for each participant to complete for each training session?

11-13. depends on TrainingTypeID=1 or 2.

 

[TipsyWolf]

this database could become enormous

i will try to change field type where its possible to optimize it. when i know its responses like 1 to 9, i set it as "byte" so it doesnt take too much space and run queries faster i guess.

 

[TipsyWolf]

i tired almost dozens of design structure and this works for me like it should with no mass records in query because i did bad job with relationship set up.

 

[The_Doc_Man]

With regard to the FullName question, my genealogy database tables do not contain such a field. They have PFX (Prefix title), First Name, Middle Name, Last Name, and SFX (Suffix title). However, when I am looking things up on a form, I have a QUERY that calls a text-string function that merges the name parts and removes extraneous spaces, tabs, zero-length-strings, and occasionally, nulls. (The nulls come in because of the unique structure of a GEDCOM file for genealogy research.) That function is never called for bulk processing, but forms generally are limited-processing cases, rarely more than 20 names at a time. It also gets used in reports and when I'm building the tree diagrams using Excel or the "history book" using Word. In each case, the full name is used only rarely.

 

[LarryE]

View attachment 119252

i tired almost dozens of design structure and this works for me like it should with no mass records in query because i did bad job with relationship set up.

This is the design I am using. I did remove the Trainer table relationship with the TrainingSessions table because it served no purpose. I still assign a Trainer to each session but it is no longer a required Foreign Key relationship.

 

[Pat Hartman]

I strongly recommend not mushing employee name. WE all know it is wrong. The OP may never have a problem if he never has to write a letter or sort differently, but who knows what requirements come down the pike in the future. So why not just do it right the first time? It is not more difficult but what IS difficult is separating a mushed name if you ever have to.

 

[The_Doc_Man]

This is one of those rare cases where programming differs from real life. It is FAR easier to put things (like... names) together than it is to correctly take them apart.

 

[LarryE]

I strongly recommend not mushing employee name. WE all know it is wrong. The OP may never have a problem if he never has to write a letter or sort differently, but who knows what requirements come down the pike in the future. So why not just do it right the first time? It is not more difficult but what IS difficult is separating a mushed name if you ever have to.

Has been changed to LastName, FirstName, Middle. FullName will no longer be used.

 

[Pat Hartman]

Excellent but why not also include prefix and suffix? People who have titles like to be addressed with them The OP can make them optional if he wants to.

 

[TipsyWolf]

but why not also include prefix and suffix?

we dont use it really in my country \ culture so this would be useless in this db.

 

[Pat Hartman]

So, you wouldn't address a doctor as doctor x? OK. Then omit it.

 

[TipsyWolf]

So, you wouldn't address a doctor as doctor x?

yes, if i call him or standing just in front of him - i would use his name and middle name. middle name is MUST to give him a sign of respect.
no doctors, no misters. just his name and name of his father.
he can be a doctor, but its nothing more than just his job title that's it. Father's name is much higher in "hierarchy" than his job tittle.

just a bit of culture diff.

 

[Pat Hartman]

Thanks.