Will this create problems later on?

[redapples]

I have a mulitselect list box from which the selections are stored in a table. I have been thinking about the following.

I collect the results of the selections in an array. As an array does not have a data type which can be stored in a table I have two possible solutions.

1.I can store each element into a seperate field e.g.

         With rs
                !xtratext1 = xtratextselect(0)
                !xtratext2 = xtratextselect(1)
                !xtratext3 = xtratextselect(2)
                !xtratext4 = xtratextselect(3)
                !xtratext5 = xtratextselect(4)
                .Update
                .Requery
                .Close
        End With

2.I can store the array as a text field using the following

    strchosen = Join(StoreInTblArray, ",")
    rs!teststorearray = strchosen
    rs.Update
    rs.Requery
    rs.Close

For form opperations either method works fine and I know that only 5 values can be stored in the array.

My question is am I making a rod for my own back here? In reports I can seperate each value by using the experession

one: splitintostrings([teststorearray],0)

to relate to the function

Function splitintostrings(strString, iwhich As Integer) As Integer
    strString = Split(strString, ",")
    If UBound(strString) >= iwhich Then
        splitintostrings = strString(iwhich)
    Else
        splitintostrings = 0
    End If
End Function

This would be a nested query and seems to work fine. I can use this query later on to provide text versions of these codes. I am in the early stages of this and I wonder if anyone has an idea of any problems I might run into if the data in the db becomes larger?

any thoughts welcome

redapples

Want the best answers? See FAQ181-2886

 

[PaulBricker]

This is just my opinion but anytime data is stored in an unnormalized manner, there is always the possibility for problems down the road. The multi select list box does lend itself to unnormalized but I would suggest resisting the impulse to store your data that way.

Paul

 

[SkipVought]

Ditto to Paul. Generally, unless there are other issues relating to processing efficiencies, it is best to store data in a normalized structure.

Skip,

http://www.TheOfficeExperts.com

 

[redapples]

Ok so you would suggest storing the selection from the listbox in seperate fields?

What type of problems are likely to occur?

I do need each record to be able to have more than one selection, otherwise the reports would double (triple &c...) count those records that have more than one reponse.

Exactly how does this break normalization (i.e. which normal form are you refering to first, second...)?

A bit of background
I am recording service user responses to services provided.
I have a table that records the interventions and some of these require a recorded response. These responses are stored in a seperate table. I would really, really not want to change the UI to enable a selection of multiple responses say in a one to many relationship between the intervention and the response (currently one to one). Therefore I am resisting multiple record of a response relating to one instance of service provision or intervention. Is this fussy logic?

I appreciate the help so far but just need a bit more info to justify the change.

Redapples

Want the best answers? See FAQ181-2886

 

[redapples]

Ok I have had another thought while thinking about normalization.

3 tables

Intervention Response_Notes Response

IntKey RespNoteKey ReponseKey
FKIndividual FKIntKey FKRespNoteKey
Criteria Notes FKTextofResponse
Indicator

This would have a one to one between Intervention and response_notes but a one to many between response notes and Response.

Then the records from Response can be used to store and populate the multiselect listbox. This would free up the need to restrict the number of responses also. It would fit my UI and also allow me to report more easily and effectively the number of interventions and the responses.

Does this seem to you guys to be a better solution?

Redapples

Want the best answers? See FAQ181-2886

 

[SkipVought]

red,

This seems odd, if I am understanding you correctly.

Your user can make multiple selections, but your current database structure has a one to one relationship of responders to responses?

Furthermore, even though the responder CAN select multiple responses, you do not want to modify the structure to accomodate a one to many?

Skip,

http://www.TheOfficeExperts.com

 

[redapples]

Skip
To clarify :
The one to one relationship is on reponses to intervention, not respondees. A respondee can have mulitple interventions of the same or varied types.

secondly as per my third post I am willing to change the structure but would rather not change the UI. Achievable I believe.

The various responses, to an intervention, are not mutually exclusive for example an respondee with an eating disorder might find that it is interfering with their life and that they are unable to access a service to gain assitence or when they enter a service can feel unsupported in managing their eating. These all can reflect one intervention. So the problem is how to allow the input of the many potential responses in the simplest possible UI that will only be counted against one intervention when reporting.

Did you see my third post?

If none of the solutions I have posted work what will?

Once again thanks for your input.

Redapples


Want the best answers? See FAQ181-2886

 

[CajunCenturion]

In answer to you earlier question, it is a violation of First Normal Form to have aggregate data within a single record of a table. So whereas joining the five answers into a single field would be a 1NF violation, storing these results in five separate fields would not be a 1NF violation.

Under no circumstances would I adovcate joining the fields together. As a quick example, what would the query look like to select ID's from the table where interfering with life is true, but gain assistance is false.

Whether or not to split these 5 selections into another table has to do with the nature of these selections, and the possibility/probability of adding a 6th selection. The question is this a true 1 to many relationship, or is it many one-to-one relationships, and it sounds like from your follow up posts, that it may be many one-to-one relationships. Therefore I would lean towards five separate fields in the main table, for again, as in my previous query, it now becomes quite simple, and better performing than doing a join across another table to handle such a query. The downside is that if a sixth response comes into play, then you'll have to add another column to your table.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[redapples]

Righto Cajun,

So basically there is a toss up between normalisation and scaleability. Will need to put my thinking caps on a bit more for this. At this stage the reports need not look at whether one response is true or false in comparison with another but if this were to be the case in the future then I guess I might cause myself problems with how to ensure this report is possible. Never easy is it? :-(

Want the best answers? See FAQ181-2886