Table Structure Help

[EricTheRed]

Sorry the title is so ambiguous, but I really couldn't explain this clearly in several words. That being said, here's the more long-winded version :

I am unsure how to "properly" structure this relationship: Each person in my database has four* "extra data" fields associated with them. The whether the field is active (and can receive data), titles of these fields, in addition to the possible values for these fields, are to be defined by the user. Imagine a limit-to-list combo box where the label is the "field title" and the items in the combo are the "field values". How to have the user edit these values is not the issue; creating tables to store the values is. All I have so far is a table like this:

tblOpFields:
FieldID (Number identifying the optional field with which the )text is associated
strFieldText (A possible value for the field)

And I don't know where to go from here. How should I associate each "extra data" field value with each person? How should I store the field titles? Help! Thanks in advance.

- Eric

* Four is the current amount, but it is possible that this value will change.

 

[Jadar]

Eric,

Looks like a 1-Many relationship to me:

A tblPerson record has many(1 or more) tblPersonField records.

Your structure for tblPersonField is basically as you've suggested:
{PersonID, FieldCode, FieldValue}

where FieldCode is a FK (Foreign Key) to the tblField table.
Presumably, tblField would have a structure like this:
{FieldCode, FieldName, FieldType}

But a problem with this is that although tblField identifies the datatype of each optional field, tblPersonField would store FieldValue as one generic type (you suggest Text)...

Was this any help?

 

[EricTheRed]

Yes, thank you, however my client just threw some more complications at me today(!) Here are the updated requirements:

For each extra data field:
- the title of the field
- field active/inactive (i.e. data can/cannot be entered into this field)
- possible values within the field, where more than one value can be selected* (this can be implemented, I believe, with a simple yes/no field linked to each value)
- ability to edit all of the field settings via the UI (no issue here)

Ack. I thought I had things under control, though now I seriously have no idea how to tackle this. Any help would be much appreciated!

- Eric

* The extra data fields are most likely "categorization fields". For example, one field may track a contact's attendance at multiple company events, hence the need to select more than one value.

 

[namliam]

* The extra data fields are most likely "categorization fields". For example, one field may track a contact's attendance at multiple company events, hence the need to select more than one value.

That is a 1 to many thing as suggested by Jadar

In your on current event of a form you can "simply" add something like:
If conferenceCheck = true then me.conferencedate.enabled = true
Or something like that for a subform or something.

Regards

 

[Jadar]

Eric's DB Design...

Hi Eric,

OK...the FieldTitle, FieldStatus(Active/Inactive = Yes/No) should obviously be stored in tblField(presuming they only vary by Field and not by Person AND Field).
If each Person can input multiple values against a Field then you have an addtional 1-M relationship. So,

tblPerson 1-------M }
tblField 1-------M } tblPersonField(PersonID, FieldID)

(..this actually a decomposition of a M-----M relationship i.e.
tblField M-----M tblPerson...)

And,
tblPersonField 1------M tblPersonFieldValue(ID,PersonID,FieldID)

...where ID could be an autonumber and (PersonID,FieldID) are ForeignKeys to tblPersonField. The combo allowing FieldID selection would restrict the underlying query to ACTIVE Fields, as indicated by the FieldStatus column in tblField.

Constraints on allowable values by FieldID could be implemented using, say, two extra columns in tblField: FieldMin, FieldMax...but you'll have to write code to tie this all up.
A global ValidationRule in tblField is not possible because Field-domain constraints vary by FieldID...

This is one solution, but, in the nature of db development, there are no doubt others!!

 

[EricTheRed]

Thank you, Jadar, for all your help. It was, without a doubt, invaluable. Here is the tentative solution I have chosen:

Tables
tblFields (defines all the extra data fields that can be associated with a contact):
FieldID [PK]
strFieldTitle
ysnFieldActive

tblFieldValues (defines all the possible values for one field):
FieldValueID [PK, autonumber]
FieldID
strFieldText

tblContactFieldValues:
ContactID
FieldID
FieldValueID
ysnSelected

Relationships
tblFields [1 --- M] tblFieldValues
tblContacts [1 --- M] tblContactFieldValues

User input/display
Here is an example for one hypothetical "extra data field": A label will display the FieldTitle. A text box displays a comma delimited list of all the FieldValues where ysnSelected in tblContactFieldValues is true. When the user clicks on this textbox, a separate window will open up, displaying the full list of FieldValues that the user can choose from.

For example, say the field was "Letters of the Alphabet". The text box might display "a, c, v, x" and clicking would produce a box displaying the entire alphabet to choose from.
---
Alright, so that's that. However, I still do have some concerns. Is this an efficient/quick design. The values for these fields will be used very frequently in my program, and will be displayed in the above manner quite often. Perhaps there is a way to eke more speed out, such as defining a separate series of tables for each field? As always, even though I may sound like a broken record, and help is always appreciated! (And thanks again!)

- Eric

 

[Jadar]

Eric's DB Design

Hi Eric,

Just reviewed your reply, and decided you're right, this is all getting very messy; it's too complicated. The basic structural error is mixing datatypes...I have a totally new suggestion for your DB design - but I don't have time to reply with it right now...if you can hang on I'll post the new design by Friday (12 Sep)

Yours,

Jadar

 

[Jadar]

Eric...

Hi Eric,

First of all, do you still want help with your DB Design?
If so, it is important I understand the semantic model; I currently understand this to be:

1
For each Contact, the User can define a set of Fields against which data may be input for that Contact. This Field-set varies by Contact and may also vary over time for the same Contact.

2
So, at one moment in time and for a given Contact, the system should only allow input against a pre-selected Field-set.

3
Each Contact has multiple records of the data input for their predefined Field set.


Proposed Solution
As we've already discussed, the initial DB-design is unwieldy.
The new design is now a simple choice:
Either
1
There is only one table containing Contact Field-data:
..this table will naturally have a column for each possible Field
..this will give rise to Null entries where the column(Field) does not apply, but if most Contacts use most Fields there won't be many Null entries and this would be the simplest solution
2
There is a table for each Field
..this, of course, avoids the problem of Null entries


The choice is a semantic one, based on your knowledge, but note that you will have to exercise programmatic control:
1
To add new Fields (or tables if your choice is 2) as the User defines NEW Fields, and update the related SQL / Form
2
As you move through Contact records, either disabling non-applicable Fields -according to the list of updateable Fields maintained for each Contact- ,
or,
dynamically resetting the underlying SQL to display only the applicable Fields
[Again the choice is yours...]

If you check out my profile, we can correspond by email
Jadar

PS:
[if you agree with the above analysis, I can forward a DB-prototype as an attachment - next week!!-can your boss wait that long ??!!]
RSVP...

 

[EricTheRed]

Jadar,

A quick heads-up. I PMed you, and we can continue from there.

(Sorry to have wasted a post simply for this, but I know I rarely check my PMs and have popups disabled, so better safe than sorry I suppose.)

- Eric

edit: An idiotic spelling error.