Puzzeled Design Issue

  • Thread starter Thread starter Michael B.
  • Start date Start date
M

Michael B.

This is a beginning designer's question that is probably
so obvious that on I would be stumped by it. Here is the
question.

What is the best way to add multiple categories to a
single record from a another table? I basically want to
build an input form that will have a drop down list with
categories that the user can make multiple selections on
and add new categories to when adding a record. Would the
category selections go into a single cell and if so how
would this be setup?

Thanks so much for any of your perspectives. I'm sure
there must be a number of different ways to do this.

Michael B.
 
You would use two tables here...one for the main entity, and the other for
the categories that are associated with that entity.

Take the classic Orders, Order Details example:

One table (Order) can have many different items (Order Details) on it.
There is a common field (OrderID) in each table and a relationship defined
between them (One-to-Many = One Order has Many details).

make any sense?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
 
This is a beginning designer's question that is probably
so obvious that on I would be stumped by it. Here is the
question.

What is the best way to add multiple categories to a
single record from a another table?
Click to expand...

Well, the best way is not to do it that way AT ALL. If you have a Many
to Many relationship between items and categories, you need to
implement it *as a many to many relationship* - with a third table
storing the category selections.

You'ld have three tables: your current table; a table of Categories
listing all the valid choices; and a CategoryAssignment table related
one-to-many to both other tables. It would have a foreign key field
linked to your table's Primary Key, and a CategoryID field linked to
the primary key of the Categories table.

To enter data, you can use a Form based on your table, with a subform
based on the CategoryAssignment table. On this subform you'ld have a
Combo Box based on the Categories table.
 
Yes, that does make some sense. I understand that at
least two tables would be used, ie. a table for the
(primary records) and a look-up table for the
(categories). What I am trying to emulate is the Master
Categories function in MS Outlook in a Contact Database.

Your answer doesn't suggest whether, 'order_details'
would contain multiple entries going into a single record
cell or some other configuration. Can you explain a
little further?

mb
 
Ah Ha! I knew that it wasn't as simple as I wanted it to
be. Thanks for the explanation and suggestion. I'm not
sure that I understand it completely but it gives me
enough food for thought that I will probably be able to
make something out of it.

Your help is most appreciated.

Michael B.
 
Back
Top