Table structure advice for a newbie

[Anon]

I'm putting together a site that is a club/bar/restaurant guide. Visitors can search by venue, by activity, or by a specific date or month. I have a table that contains information on the venues (address, contact information...that type of thing) and I'm having trouble figuring out the most efficient way to associate each venue with the activity (or most likely activities) that it offers.

Any advice? Thanks in advance.

Robert

[gio2k]

Use a many to many relationship:

Table Venues
(
Venue_id integer not null primary key,
...
)

Table Activity
(
Activity_id integer not null primary key,
description varchar(255,
...
)

Table Venue_Activity
(
Venue_id integer not null,
Activity_id integer not null,
..
primary key(Venue_id, Activity_id)

HTH

-Gio-
)

[[deleted]]

Just to clarify Sergios post a little:

Create one table with the address data etc.
Create one table that contains only the data about the activities.
Then create a third table that links the address to the activity.

So the venue table would be like
Table Venues
(
Venue_id integer not null primary key,
address
name
phonenr
)

and the activities table would be like

Table Activity
(
Activity_id integer not null primary key,
description varchar(255,
...
)

and the table that links them would be like:

Table Venue_Activity
(
Venue_id integer not null,
Activity_id integer not null,
startdate
stopdate
hours
price
primary key(Venue_id, Activity_id)

Every time you want to add an activity to a venue, you create a new record in the venue_activity table, with the venue_id and activity_id of the venue and activity.
So if a venue has three activities, the venue_activity table has three records with that venue's id.

[Anon]

The above comments are great if the activites you are planning to enter have a bunch of "commonality," i.e. several locations would have the same activity. If each location has its own UNIQUE activites, then it may make more sense to have only two tables:

locations
loc_id
address
etc

activity
act_id
loc_id
activity_info

now... if each activity has commonality in terms of 'type' but varies in terms of the specifics, you should use the variant vincent suggested, adding fields as appropriate to contain the unique data of each activity to the crossover-table.