problem with relational design

[activethistle]

Hi
I'm sure this problem has been posted before. Can anyone point me to it?

I am building a db and i need 2, 3 or 4 tables. I dont really know.

I have a list of numbered conditions, 1.1, 1.2, 1.3, 2.1,2.2 etc.. with some related text. i.e. 1.1 Has learned MS Access
1.2 Has learned MS Word etc...

I have a list of dates when said conditions have been met. e.g. 13/02/06 - addressed condition 1.2

Problem is, conditions may be met on several occasions, so single entries are not suitable. Can anyone help me design the table structure for access, I'm having a nightmare with keys, indices and duplicate records.

BTW I also neeed to print reports of achieved conditions by condition number as well.

Thanks very much you access gurus you!

Active



Never, ever walk up to a computer and say (don't even think), "This will only take a minute!"

 

[Lilliabeth]

You made no mention of people, but I think the purpose of the db is to record when people met conditions. Is this correct?

Also, the "related text" - are you saying that this text is the description of the condition? If so, can a person meet the condition "has learned Word" more than one time?

Or maybe it is related to the person and the condition.

Please clarify the purpose of the database.

 

[lespaul]

and read the Fundamentals document link below.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[activethistle]

lilli - yes someone can learn word more than one time. (different aspects of it) I think on reflection my table requirement needs looking at. If you have any suggestions...?

lespaul - thanks i'm reading it now!

Never, ever walk up to a computer and say (don't even think), "This will only take a minute!"

 

[Lilliabeth]

If the purpose of the db is to record when people met conditions:

Table for people. Maybe name it tblParties. Maybe name the PK intPartyID.

Table for Conditions. Maybe name it tblConditions. Maybe name the PK intConditionID. One field, maybe named strConditionCode will contain the 1.1, 1.2, etc. Set the index property of this field to Yes, no duplicates.

Junction table. Maybe name it tblPartyConditions. Maybe name the PK IntPartyCOnditionID. Set a unique index across the combination of intPartyId, intConditionID, and dtmConditionMet.

 

[BobRodes]

<yes someone can learn word more than one time. (different aspects of it)

You may need to define your operating rules more clearly here. When you say "different aspects of it" you're suggesting that it matters which aspect of Word a person has learnt at any given session. If so, I'd have "classes" or whatever, and some would be called "Word I", "Word II", "Word III", etc.

DON'T...repeat DO NOT attempt to put tables and stuff together before you've set in stone your business requirements. Don't worry about indices, keys, whatever, until you know what kind of data you want to keep. Decide exactly what you want to do. Then do it.

I've written over 100 database applications from scratch, large and small, and I find the first thing that I do, with very few exceptions, is ask questions about the problem domain (i. e. the business) that the application owners haven't answered and have to think through. Here's a case in point: how do you want to handle multiple sessions on the same application? Do you care for some reason that a class is for Excel or for Word? Do you have some sort of competency certification for each Productivity app? Or would it be ok just to have all the classes in a table be unrelated to one another, even if some of them are actually different classes for the same app? That sort of thing.

HTH

Bob