Many 1:1 tables? Or...

[Pyro]

Hi.

I am working on an application that records project result data. Most of the database has been mapped out and is relatively straight forward. However, each project has its own unique subset of result data, somewhere between 2 and 15 intrinsic fields.

Currently there are 15 projects. And it is a certainty that new projects will be added in the future. Although there wouldn't be more than a few new projects added each year.

I see two options for recording this unique subset of result data:

1. Create a 1:1 table between project results and each sub-set of unique data.

Pros: Data types, validation rules etc can be explicitly set.​

Cons: There will be a significant overhead for each new project where a new table will need to be created (not to mention data entry forms etc).​

2. Under each project create a template table that houses the sub-result questions to be asked, and under project results create a sub-result table that references these questions and allows a result to be recorded.

Pros: New projects would be simple to start - no new table/form etc​

Cons: Data types, validation rules cannot be easily applied. The data will be recorded in a text field, meaning that combo boxes etc cannot be used.​

Considering the above, has anybody ever dealt with a similar scenario, or does anybody have any opinions/thoughts on the best path forward?

Happy to hear and discuss any opinions.

 

[speakers_86]

What is this subset of unique data? Can you elaborate on that? Depending on your answer, I'm wondering if you can have two tables:

tblProjects
-ProjectID
-Other Stuff

tblProjectData
-ProjectDataID
-ProjectID
-Field1Label
-Field1
-Field2Label
-Field2

Have enough blank fields of various data types to cover all you need. If you are able to list the different possible labels that could be used, those should be detailed in a third table. This may not be the most elegant solution, but I think you can make it work.

 

[Pyro]

The subset of result data could really be anything.

Some examples:
- A description of the site where a sample was taken.
- The depth (in metres) under water where a sample was found.
- The abundance of females in a specific area.
- Whether X characteristic was present.
- ...

So the value could be a string, number, boolean etc...

This part of the db is mapped like this:

tblProject
- ProjectID
- ...

tblProjectResult
- PRID
- ProjectID
- ...

The data in question comes in at this next tier.

The idea behind option #2 in my OP would look like:

tblProjectResultTemplate
- PRTID
- ProjectID
- PRTLabel
- PRTDataType
- PRTValidation

Then a table under the result layer:

tblProjectResultTemplateData
- PRTDID
- PRID
- PRTID
- PRTDResult

Code would validate the data type and any other validation rule at the point of data entry based on the pre-selected values in the template table.

I am definitely still staring at the problem waiting for an answer to jump out at me...

 

[Pyro]

The thing that irks me most about the above scenario is that i would need to present the questions in a continuous form or datasheet, meaning i couldn't have some of the more simple answers limited to the results of a combo box...

 

[spikepl]

Have a read:

https://en.wikipedia.org/wiki/Entity–attribute–value_model

 

[gemma-the-husky]

you never ever NEED a 1:1 link

it may be convenient in some special cases, but a 1:1 link is never required. I am sure you are describing a 1:many relationship, but your tables are probably not understood or defined correctly

what you have is these with a 1:many relationship

tblProjects

tblProjectData

it is possible that you do not actually need the tblProjectData, and that all the project data could be held in the main table. It's a matter of what you are trying to do.

 

[The_Doc_Man]

From your comments, the problem is that the projects have unique results. My question is whether there are common results among the 15 projects. I.e. maybe you have 15 projects with 2-15 result sets - but are there results in the "lesser" results sets that don't occur in the project with the most results?

I might see this as a (somewhat messy) case of a project table, a result/question table, a junction table to the possible result descriptions, and then a results table driven with a compound key derived from the "possible results" table.

The project table has its PK as some sort of project ID. The Result/Question table has a PK for each possible result type.

The result-junction has two values of interest - the Project PK and the Result/Question PK. If you have an entry in this result-junction table, the result is appropriate for the project. (If you are familiar with "Interrogative Logic" you would know that the project "condones" the attribute.)

Now, the ActualResults table has a compound PK - the Project ID and the Question ID. The value for a particular condoned result is specific to the project. The data type for the given question is in the junction table (and in the Result/Question table). You always store the result as a string (unless it could be a memo, and I hope it can't be for the sake of this discussion.) You use a CASE statement to translate the value to its natural form based on the data type by using CDATE, CBOOL, CLNG, CINT, CDBL, etc. on the stored string. Perhaps you can write a function that returns a Variant for the value in question, because Access automatically translates variants in expressions.

Nobody said this would be pretty - but it has been done this way in the past.

 

[Pyro]

Thanks everyone for your input.

Spike, that link was very interesting and google offered a ton of additional info on the EAV model. Doc, your response heads down this path as well and is inline with option #2 from my OP. So far this is how i am mapping it out:

tbl_Project stores data specific to the project (things like project title, funding details etc...)

tbl_Project_Question_Unique has the following fields:
- PQUID (Autonumber, PK)
- ProjectID (N, FK)
- PQU_Question (T)
- PQU_Data_Type (T) - values like string, double, long etc
- PQU_Data_Type_Format (T) - values like percent etc
- PQU_Data_Validation (T) - I am using a series of preset codes and am going to use a function to validate the result of these codes, this way i can set a value limit or acceptable value range etc.

tbl_Project_Result stores general data for all projects (result date, person recording etc...)

tbl_Project_Result_Unique stores the values for the unique results appropriate to the project. The following fields are included:
- PRUID (Autonumber, PK)
- PRID (N, FK from tbl_Project_Result)
- PQUID (N, FK from tbl_Project_Question_Unique)
- PRU_Value (T)

The plan is as follows. Every time a user initiates a new result set, they are prompted via a dialog form to select the appropriate project, the record is created with a few assumptions pre-filled (Person logging the result, date/time stamp etc), the unique project related questions will also be pre-populated into a subform for this result set, so the user is only presented with the unique questions appropriate to this project.

Personally, i think this method will be pretty straight forward. Managing data types and validation rules will be the most tricky part, but a few decent functions triggered by the beforeupdate event of the result entry text box control should do the trick.

We will see if this turns out to be intuitive enough for the end user.