1 to 1 tables for large number of fields 1

[PaganBilly]

I am developing a database with 1000 records. For each record I will be storing about 4000 items of information. It is effectively a flat database and my planned structure consists of one master table with several (20+) 1 to 1 related tables.

I am concerned that I will hit the limit for the number of tables related to one field. Also I believe that Access experts would not regard this as a good structure. But how should I structure it?

Any advice appreciated.

 

[beetee]

Could you shed some additional light on the information that you are storing? I think listing all 4000 types of information may be a bit over the top; but perhaps you could provide categories in which the data fits.

 

[PaganBilly]

Most of the data consists of test results. I am trying to categorise the fields such that each table has 100 to 200 items. Also, some of the data is about events which happen up to 4 times. In these cases, I am putting event1, event2, event3 and event4 fields into seperate tables, so that the event4 table, for example, might only hold 50 records as only 50 people got as far as event4. For data entry/analysis, the user will see all 4 events on one form.

 

[beetee]

I think your plan sounds good, you have little choice in the matter.

Here are a couple of ideas that may or may not help:

1) Put all events in a single table, with an event number, a link field, and the event data

E.g. test event # result
1 1 false
1 2 true
1 3 false

This more or less implements a relational array. You may find other uses for it as well. I used this concept in a speech therapy charting application for test results.

2) Perhaps you can store some information in text or memo fields in a parsed format, and packing it and unpacking it to/from unbound fields in your form (more or less implementing an array).

Of course, this could be bad or good if you are attempting to query the data later, and may cause some (probably well-deserved) backlash from relational purists.


e.g. if there set of related numeric results, pack them into a CSV list of data (perhaps a set of trials).

It would take some VB code to pack and unpack the data.

 

[MichaelRed]

The 'current' thinking within the Relational Database Gurus is that deep is better than wide. Your schema 'looks like' the wide approach.

Taking the test grades item, your outline is like the conventional teacher's grade book, listing the student's entire grade history in the single record, with a column for each possible grade. The 'modern' relational db would approach each grade as an individual record, listing the [Student], [Date], [Grade]and (possibly) an [identifier] (description of the grade item - e.g. 'Test A'.

Similar approaches can (should) be utilized on many 'groups' of fields in your db. You are probably correct in thinking that splitting 4K FIELDS into sub records with a 1:1 relationship will run into some problems. Even the 'industrial strength' relational dbs have limits on the numbner of fields allowed in a recordset. Ms. A.'s limit (which is a fairly common one) is 255 -with a caveat- that indicies, sorts, relationships, having & where 'fields' count toward the limit. There may be *are?) others 'counters', so construction of a recordset of 4K fields in most any manner will be QUITE a challenge.

I would suggest some review of realtional database strategies before starting this process. Another suggestion is to review the purposes of the information to be collected. Back to the old school "Who, What, When, Where, and Why" of each item of information. I would expect that such a review would reasonably reveal 'logical' groupings of the information which aid the development.

One approach might be to start with the 'output'. the reports (and possibly Forms) which the various users will actually use in their daily work. For each group (or even individual), define the subset of information they will utilize. Based on this, develop the various tables as groups of related information which are commonly accessed.

From the question posted, I assume that you have little experience in relational database use or programming and, frankly, think that you will be jumping into the deep end of the pool. While there are many helpful resources, I would not suggest that you rely on public bullitin board sites to get you through this project. It is -in my opinion- well beyond what a novice should be attempting. I do not know your total situation, but unless refusal would -by itself- be a dire consequence, you need to garner some experience on smaller projects before attempting this.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places