Array data type in Access (or a better design?)

[Guest]

I need to design a database that keeps information about documents. Each
document will consist of multiple topics. Each topic will have attributes.
A given topic can appear in multiple documents. Each time it appears it will
have a level (as in level in a table of contents) and an order (e.g. 2nd item
at the third level) and a parent (the topic that is its parent).

I thought about having a Topics table that would contain a list of all the
topics each having a title, unique ID, and meta data about its attributes. I
also pictured a Documents table with a title, unique ID, an array of topic
IDs, an array or level's, and an array of orders. A given document could
consist of hundreds of topics which is why I was thinking of storing the
topic IDs, levels, and orders as arrays.

I haven't seen a data type in Access that would correspond to an array. I
want to store those things as arrays to save having hundreds of elements in
the table. Does Access have a way of doing this? If not, can anyone
suggest another design to handle this? Keep in mind that a given topic can
appear in multiple places so the level and order will differ.

Thanks!

Bob

 

[Guest]

Start with:

tblDocument
DocumentID (PK)
DocumentTitle

tblTopic
TopicID (PK)
TopicName
TopicDescription

Now, you need a third table to tie these two tables together.

tblDocumentTopics
DocumentTopicID (PK)
DocumentID (relates over to tblDocument)
TopicID (relates over to tblTopic)
LevelNumber
OrderNumber

Use relationships to tie tblDocument and tblTopic to tblDocumentTopics.
Each record in tblDocumentTopics will relate a document to a topic, and
include the Level and Order of that topic in the document. This allows you
to relate a topic more than once to a document, with a separate level and
order each time.