advice how to approach db design to allow variable number of project specific fields

[chaeljc]

Hi all

Ifpossible im am looking some advice on how I should approach the design of a newproject driven ms access database where each project has a variable number and type of fields which are specific only to that project and require user input into these fields for each record associated with that particular projec

Note: Thisis a continuation of the design of a system discussed in the previous thread titled:
'possible multitable many to many relationships design confusion'

whereJUNE7 gave me some great advice enabling me to sort out a sizeable chunk of theunderlying table structure and relationships. Unfortunately shortly after that advice I was pulled off onto anotherproject and i am only getting back to this design now.

For your information only, below is a little outline info onthe build

· Itwill be in access 2002-2003 format.
· Itwill be run on a local area network.
· Tableswill be normalised
· Theteam using it will be 100 users. Maxsimultaneous - approx 50. NB with regardto multi user issues I have had another system running with the 100 users asabove for some time. Therefore for thepurposes of this thread I am not so concerned with multi user issues and wouldprefer to focus on the issues which come below after ‘Primary Advice Sought’.

Approx 70K records
· Staffthat input or amend data will not be working on other users records.
· Alongwith Forms queries and macros, VBA will be employed to action some aspects ofthe system. Some basic SQL may becontained with in VBA proceedures.
· Iam reasonalbly competant using VBA

· Thedatabase is PROJECT driven.
· Everyrecord belongs to a project.
· Eachproject will have a potentially unique number of work items known asSTAGES. For example Project A could have1 stage. Each record associated to Project A will requirethe completion of that one stage for work on that record to have beenconsidered to be complete. Project Bmight have 8 stages. For work on recordsaccociated with Project B to be considered complete, all 8 stages must becompleted.

· Asingle officer is assigned to work a stage.
A record with multiple stages can have different officers working thedifferent stages.



· Thestages are worked sequentially by each assigned officer in turn.



Primary Advice Sought

In theprevious thread with excellent guidance from JUNE7 I was able to sort the relationships neededbetween the Projects, Records, Stages and Officers tables.

Here is the bit I am unsure of on how to proceed….

Focusingin on the records table, this table contains what I term as the ‘core data’only. This is data that all recordsregardless of Project will contain. I.E. Date added to database,Reference & Name.

Myproblem is that in addition to these Core fields, the records for each projectwill have additiional fields that will be specific to only that project.

Thefields could be 1 extra or many extra, and of any data basic data typei.e. text, number (double?) date andhyperlink.

Officers must be able to input into these fields.

QUESTION
Keepingnormalisation rules in mind, how do I set up the database to allow for thesepotential extra project specific fields? The aim is to create system which once set up will accomodate projects with variable work stages and variable project specific additional fields of varying data type, without the need to for example add additonal tables or fields to tables every time a new project is created.

Theapproach I have taken in my premliminary modelling is in addition to the CoreData table, to create additionaltables, one for each data type which are linked to the project and appear as subforms to the main project setup form. These are used to define the names (labels) of any project specificfields.

I thenhave other additional tables linked to projectRecords which contain the actual datafor each of the additional project specific fields.

Eachuser has a ‘work list’ of records assigned to them. The form detail contains the core data ofeach record.

Theform footer contains sub forms which show the project specific fields for eachrecord and are ready to accept data.

Althoughthis approach is actually working ok it feels a little clumsy or clunkey as theform footer has always to contain multiple sub forms, each there to accomate anypotential project specific fields for a specifib data type. So for example, thedemo worklist has 4 sub-forms in the footer to hold text, dates,numbers,hyperlinks data for the selected record from above.

QUESTION
Isthis approach correct? Or is there amore elegant solution that would enable one to have for example one sub form inthe footer which contains all the project specific data labels and data fieldsno matter what their data type?
The subform for example might have, reading left to right, the field label, a labelindicating data type and then the field to hold the data. i.e.

Label Type Data
Address Text 2 Mountain Way Birmingham
DOB Date 01/01/1970

Note the data fields need to be updateable to allow userinput

Onestep better again would be to have a similar construct on the Project setupform. For example one sub form where youcreate the label name, select from a combobox the data type for that the dataholding field will contain.


apologies for the above being a little long winded, i hope my explanation is coming accross ok.

I look forward to any questions you might have and would be most grateful for any thoughts or guidance youmay have.

Many thanks

Michael

[jzwp11]

What are these additional details of a project? Can you provide some examples? I am thinking that you do not want fields but rather related records.

[chaeljc]

many thanks for taking an interest in my database diffs!

to hopefully explain things a bit better i have included a word attachment which includes further explanation and a rough diagram of 2 example projects setups.


if you have any further questions or queries please do not hesitate to get in touch.

kind regards

michael

[jzwp11]

I took a look at the information you attached. I think allowing the users to create fields will cause a lot of problems. I hope you realize that when you create new fields any related forms, queries or reports will also have to be updated with those new fields. Are you going to expect your users to do that? What you describe are activities, these should be records & you can allow your users to create new activities as needed. You can just allow a text field for the user to name the activity or if that activity might be used over the course of other stages in the same project or in stages of other project then it would be best to store those activities in a table and relate it to the applicable stage or stages as necessary. So related to your stages, you can have a table that captures the particular activities. Now regarding the detail of the activity (i.e. the name of the boat, the color painted, the yes or no to the test, the number of life jackets etc.), you can use a couple of different approaches. You could define multiple detail fields each of a different type and associate the data type with each activity in tblActivities. You would then use code in your form to control which control shows up depending on the activity selected (via a combo box). Alternatively, you could still define the data type of the activity in tblActivities but have only 1 detail field in the tblStageActivities. That one field would be a text field and you could test whether the value entered is a number for an activity that requires a number detail response using the isNumeric() function or you can test whether the detail is Yes or No for the Yes/No etc.

So a table to hold the activities

tblActivities
-pkActivityID primary key, autonumber
-txtActivityName
-txtDetailInfoType (you define whether the detail for this activity needs to be text, number or yes/no)

So the records that would be in tblActivities might look something like this

pkActivityID|txtActivityName|txtDetailInfoType
1|Boat built (include name)|text
2|Item painted (include color)|text
3|First Aid box installed|null
4|Tested (indicate test outcome as yes or no)|yes/no
5|Life jackets installed (indicate number)|number
6|Spare tire installed|number
7|Stage Started|null
8|Stage Finished|null


Now a table to relate the activity to the stage


Approach 1: Multiple detail fields each of different datatype


tblStageActivities
-pkStageActID primary key, autonumber
-fkStageID foreign key to your stages table
-dteActivity (date of the activity)
-fkActivityID foreign key to tblActivities
-txtActivityDetail (a text field)
-logActivityDetail (a logical or yes/no field)
-longActivityDetail (a long integer number field)



Approach 2: One single activity detail field with a text datatype. You control the response provided via the txtDetailInfoType field value applicable to the activity chosen.



tblStageActivities
-pkStageActID primary key, autonumber
-fkStageID foreign key to your stages table
-dteActivity (date of the activity)
-fkActivityID foreign key to tblActivities
-txtActivityDetail (a text field to handle all types of responses)

[chaeljc]

hi jzwp11

aplogies for taking so long to reply but i have been getting totally hammered at work and not able to progress on the above for the bit. meantime i wanted to thank you for your reply. when i get back to that project(which i will immently) i will post up progress etc in case some one finds it helpful.

i trust your well, kind regards Michael

[jzwp11]

No problem. Just post back with any questions after you get back to the project.

[chaeljc]

Will do. Many thanks jzwp. Regards Michael