Database design for multiple list meny entries

Tezread

I have a database called nottsvcs with a design as follows:

refno (primary key)
organisation (char50)
telephone (char50
activities (list menu)**

My list menu of activities allows the user to pick more than one activity. My question is how do I change my db design to allow for storage more than one list menu selection?

Fernashes

You probably want to set up your database something like this:

ref

refno (primary key)
organisation (char50)
telephone (char50)

ref_act

ref_actid (primary key, otherwise not used)
refno (foreign key to ref)
actno (foreign key to act)

activities

actno (primary key, auto_incr)
activity

The activities table is the actual list of activities. The ref_act table is a link between the two tables, where you pretty much say "this person (refno) participates in this activity (actno).

Let me know if it isn't clear. Good luck!

Tezread

How do I link the tables using foreign keys?

Fernashes

In MySQL, as far as I know, you don't. MySQL does not support foreign keys. And if I'm wrong, someone's going to call me on it. 🙂

What you do is make those tables in MySQL. Then you can call the data in a query... you're talking about joins and such.

In your case, if you wanted to list the activities that one person does:

SELECT *
FROM ref_act, activities
WHERE ref_act.refno=1

AND ref_act.actno=activities.actno

That will return all the activities the person does where refno=1 (person with id 1).

Make sense?

Fernashes

Did this solve your problem? If so, please set the thread as resolved. If not, I'm curious to hear about it.

liquorvicar

ok Fernashes, i'll call you. MySQL does have foreign key support for InnoDB tables:
FOREIGN KEY
which includes ROLLUP (meaning that if you were to delete an organisation, MySQL would be able to delete any entries in ref_act that match the refno).

Fernashes

😊

Ok, guess I was asking for that. And you really do learn at least one new thing a day!

I think I'll go and play with foreign keys in MySQL for a while. 🙂

liquorvicar

didn't realise myself until recently. in fact, until very recently i didn't even realise you could use INNOdb in MySQL versions pre Mysql4
😊