dbase question

im planning a dbase and each record could belong to mulitpul cataogries of a
non set number ( extra catagories could be added later).. how would i store
this information without setting up a field for each catagory . .e.g.
cat1id, cat2id, cat3id each time a catagory is added

Tony

Tony Chamber wrote:

im planning a dbase and each record could belong to mulitpul
cataogries of a non set number ( extra catagories could be added
later).. how would i store this information without setting up a
field for each catagory . .e.g. cat1id, cat2id, cat3id each time a
catagory is added

Tony

Have a look at http://www.edm2.com/0612/msql7.html for some basic advice on
database design.

In your particular case, I would see two tables; the information table, and
a categories table. There will be a one-to-many relationship between
information and categories (each information row will refer to one category
row; each category row can be used more than once in the information table).
Using this relationship, you add new category data to the categories table,
and link to the new category from the information table.

e.g.

information
-----------
info_id
fk_cat_id
data1
data2
data3

categories
----------
cat_id
dategory_data

Link is...
fk_cat_id - cat_id



HTH,

Pete.
--

Peter Connolly | Macromedia Certified Dreamweaver MX Developer
http://www.acutecomputing.co.uk
Derby
UK

On Mon, 1 Mar 2004 09:28:13 -0000, Tony Chamber wrote:

im planning a dbase and each record could belong to mulitpul cataogries of a
non set number ( extra catagories could be added later).. how would i store
this information without setting up a field for each catagory . .e.g.
cat1id, cat2id, cat3id each time a catagory is added

Sounds like a many-many relationship. You need a table for categories (eg
fields of cat_id, cat_name), a table for products (or whatever you have)
and a linking table which has product_id and cat_id as foreign keys.
--
Steve
www dot flyingtigerwebdesign dot com
Hong Kong, 01-Mar-04 8:42:20 PM