Repeating groups of fields within a table?

[sts023]

I know, it's probably the millionth time some newbie has asked this, so I'm sorry if this is a patience tester, but here goes anyway.

I'm trying to set up a sort of "Criteria Table" for blind Users of a Talking Book library.

I have a Table of "Borrowers", into which I'd like to put a "fkCrit" field to link to a "pkCrit" field in the Criteria Table. All fairly straightforward so far.

Now the bit where my techniques and knowledge start to crumble.
What I'd like to achievein the Criteria Table is a list of criteria with a variable number of entries. These criteria will be entered into the table via a Form (probably), used on Borrower setup / maintenance.

The ideal would be a structure like -

'   pkCrit              Autonumber
'   fkBorrower          Long
'   CritCount           Integer
'   CritGroup           ???
'       CritFieldName   String
'       CritFieldTest   String
'       CritFieldText   String
'       CritFieldLink   String

In English, this would contain something like 2 entries of

1. AuthorForename (CritFieldName) "is" (CritFieldTest) "Agatha" (CritFieldText) "and" (CritFieldLink)
2. AuthorSurname (CritFieldName) "is" (CritFieldTest) "Christie" (CritFieldText) "" (CritFieldLink)

or 3 entries of

1. AuthorForename (CritFieldName) "is" (CritFieldTest) "Agatha" (CritFieldText) "and" (CritFieldLink)
2. AuthorSurname (CritFieldName) "is" (CritFieldTest) "Christie" (CritFieldText) "and" (CritFieldLink)
3. Title (CritFieldName) "does not contain" (CritFieldTest) "Poirot" (CritFieldText) "" (CritFieldLink)

The question is, how do I set up the Table to contain a variable number of repeating field groups?
I know I could just pre-defeine a working maximum of say 20 fixed groups of criteria, and just use CritCount to extract them, but is there a more elegant (and space saving) way?

Any advice would be appreciated....

 

[Galaxiom]

Use related tables with a record for each criteria that also includes a criteria type.

fkBorrower (Long)
CritTypeID (Integer)
CritValue (String)

CritType would be another table with:
CritTypeID (Integer)
CritTypeName(String)

 

[sts023]

Hi GalaxiomAtHome...

Sorry to be dim, but I don't see how your suggestion helps.

Unless I've misunderstood, all you've done is shifted an individual criteria group out to another table, which doesn't seem to address the base problem of holding a variable number of repeating groups (i.e.criteria) in one Table.

I'm trying to achieve (again in English), a list of criteria for each person such that, for example (sorry for the typecasting)

Borrower Arnie Schwartzenegger wants any "Adventure"

Borrower Barbara Wood wants only "Romance" (but not by Barbara Cartland)

Borrower Isaac Asimov wants "Science Fiction" by "anybody", or "Crime" by anybody except "Jeffrey Archer"

as you can see, Arnie has 1 criterion, Barbara has two criteria, and Isaac has 3 criteria.

Whilst I accept that some criteria may be common amongst Uses (i.e. Author Not Archer ), for the sake of ease of understanding I'd like to hold the individual Borrower's criteria in a single place (i.e. a Crits table linked to the Borrower Table).

Does this clarify my objective?

 

[scubadiver007]

"Unless I've misunderstood, all you've done is shifted an individual criteria group out to another table"

That is called relational database design!

So each borrower can borrow from many categories and each category can have many borrowers (many-to-many relationship!)

But then you also want a many-to-many relationship between books and categories (similar to borrowers)

If it sounds complicated, it will have to be if you want to get the best out of your database.

 

[sts023]

I don't want to sound popmpous, but I know it's called relational database design!

In my 35 years as a professional programmer, I came to realise that opting for the best possible performance, whilst sacrificing clarity of purpose and ease of understanding is not always A Good Thing, especially if your code is likely to be maintained by others with less experience than yourself.

Having read the replies so far I will revert to my "fallback solution": that of having a Criteria Table linked to a Borrower Table entry, and in that Criteria Table having a preset number of available criteria, together with a count of active entries.

Thanks to all who took the trouble to reply - I'm not being grumpy, just suffering from time pressure. As a boss of mine once reprimanded me "It's no good having a nearly-built Porsche in the garage when a Ford Escort could already have made the journey!"

 

[Rabbie]

I don't want to sound popmpous, but I know it's called relational database design!

I think you are succeeding

In my 35 years as a professional programmer, I came to realise that opting for the best possible performance, whilst sacrificing clarity of purpose and ease of understanding is not always A Good Thing, especially if your code is likely to be maintained by others with less experience than yourself.

Having read the replies so far I will revert to my "fallback solution": that of having a Criteria Table linked to a Borrower Table entry, and in that Criteria Table having a preset number of available criteria, together with a count of active entries.

Thanks to all who took the trouble to reply - I'm not being grumpy, just suffering from time pressure. As a boss of mine once reprimanded me "It's no good having a nearly-built Porsche in the garage when a Ford Escort could already have made the journey!"

In a properly designed Relational database you make sure your data is properly normalised. Read the article on this in Wikipedia as a starting point and also do a google search on the topic.

Properly normalised data makes the task of getting information in and out of the DB much easier. Performance gains are just an incidental benefit of this process.

 

[boblarson]

Denormalization for performance is one thing but trying to design tables with "variable numbers of repeating groups" is not denormalization for performance. In fact, it is highly likely going to HINDER performance because you will have to try to search on many fields.

You do want the extra table with the ROWS of data so that Access can find the data you want efficiently, ESPECIALLY in this case.

 

[Galaxiom]

Unless I've misunderstood, all you've done is shifted an individual criteria group out to another table, which doesn't seem to address the base problem of holding a variable number of repeating groups (i.e.criteria) in one Table.

You have misunderstood. It changes the record structure of the Criteria table from one record per Borrower with a field for each criterion to multiple records per Borrower. Each borrower has one record for each different criterion.

The criteria values of all Criteron Types and Borrowers are held in a single field for each. The type Criterion type (an Ineger which represents Author, genre etc) is held in another field.

This allows the database to identify the Borrower, Type and Criteria string values by looking in a single field for each.

eg. If TypeID 2 is AuthorSurname then a search might be:
WHERE BorrowerID = 234 AND TypeID = 2 AND Criteria = "Asimov"

Your proposed structure reqiures dynamic queries to insert the fieldname.

 

[Galaxiom]

In my 35 years as a professional programmer, I came to realise that opting for the best possible performance, whilst sacrificing clarity of purpose and ease of understanding is not always A Good Thing, especially if your code is likely to be maintained by others with less experience than yourself.

Clarity of purpose and ease of understanding is achieved by good program structure and sensible commenting. A well constructed program helps build the knowledge of those less experienced who follow.

Having read the replies so far I will revert to my "fallback solution": that of having a Criteria Table linked to a Borrower Table entry, and in that Criteria Table having a preset number of available criteria, together with a count of active entries.

Your "fallback solution" is flat unstructured data. You might as well include the Criteria records in the Borrower table as there is nothing to gain by using a separate table.

Recording a count of the active entries is a breach of the most basic principles of normalization and adds unnecessary maintenance overheads.

Others who face the unfortunate task of maintaining your clumsy database will assume that you know nothing.

As a boss of mine once reprimanded me "It's no good having a nearly-built Porsche in the garage when a Ford Escort could already have made the journey!"

It is far worse when you are halfway through the journey and you realise you are driving a Lada that will neither go the distance to the destination nor get you back to the garage.

The data structure is the foundation of the design. What might look to a novice like an expedient move to choose a flat structure will waste far more time later with increased complexity every time you write a query. When you realise your mistake it will be too late to salvage anything significant from the queries, forms and reports.