Retrieving Matching Data From Access DB

[Jeff Gaines]

I have an app that stores/retrieves data in an Access 12 database, it is
written in C# and uses ADO.NET to store/retrieve the data.

There is one table containing main categories which uses the record number
(unique, auto number) to identify the category name.

A second table contains sub-categories and includes int fields containing
the MainCatID and the SubCatID - So I can retrieve all records where
MainCatID = required mainCatID.

I then have a further table of records (record table) each of which has a
MainCat and which can belong to any number of SubCats within that MainCat.
The MainCatID is kept as an int and the SubCatIDs as a string which is a
comma separated list such as "3,12,13,18".

My question relates to record retrieval from this third table where I need
to retrieve a record where, say, MainCatID is 3 and SubCatID is 12. The
function prototype is:

internal static List<JCurrentAdvertsData> AllRecordsForCategory(int
mainCat, int subCat)

so I know the mainCat and subCat I want when calling it.

Currently I retrieve all the records matching the mainCat, convert the
string of sub-categories record by record to a List<int> and add the
record to a List of records to be returned if the List<int> contains the
required subCat. This works and is OK with a limited number of records in
the database but I am concerned about how it will scale when there are,
perhaps, several thousand records to check. There is no limit on the
number of sub-categories the record can belong to otherwise I could have a
field for each and probably make life easier.

This may be more a generic programming question, but I thought I would ask
here first in case there is anything within the NET /ADO.NET framework
that would help.

Any thoughts/ideas would be appreciated, if it's a stupid way to keep the
data then I can change that - the important thing is to be able to pull
matching records in a reasonably efficient way. I could add a linking
table containing MainCatID, SubCatID and Record Number which would have
several records for each record in the record table - one for each
sub-category the record is in for instance but I would appreciate any
other thoughts/ideas.

Many thanks

 

[Kerry Moorman]

Jeff,

Storing multiple values in a single column is a huge design mistake.

Your best bet would be to re-design the table according to generally
accepted standards.

Kerry Moorman

 

[Jeff Gaines]

Jeff,

Storing multiple values in a single column is a huge design mistake.

Your best bet would be to re-design the table according to generally
accepted standards.

Kerry Moorman

Hi Kerry,thanks for the input

I am not a DB designer as you may have surmised! My issue is that since
there could be any number of subcategories I can't decide in advance how
many fields there should be if I use one for each subcategory.

In the meantime I have added a link table that I can search on main
category and subcategory, and which returns a record number.