Optimal Table configuration?

[pontifex]

I'm doing a project to create a sort of scheduler widget for a group of friends of mine and I have no idea how to structure the tables for optimal queries and joins.  Here's a contrived example of the kind of data I'm going to be dealing with:

 

Say user Alice wants to get together with user Bob.  Alice has a set of things she wants to go to, call them the 'restaurant', 'movies', 'shopping' and 'park'.  Bob has a similar set of items that overlap in a few places, but not all; Say 'arcade', 'park', 'restaurant', 'batting cages' and 'the back seat'.  The two want to get together to do something, so this obviously involves a join on the database holding their places they want to go.  We're going to get an intersection at 'park' and 'restaurant'.  But, I have many more things which my users want to get together for, numbering in the 20s-30s.  So obviously some segmentation is in order, but I'm not sure where to segment.

 

I struck on the idea to segmenting these items into the time of day that they take place.  For example:

 

Night Time -> 'restaurant', 'movies', 'the back seat'

Morning -> 'park'

Any Time -> 'arcade', 'batting cages', 'shopping'

 

Where 'Night Time', 'Morning', and 'Any Time' are tables.  Then I can do joins on the tables to find intersections.  I'm just concerned about the overhead of:

 

a) making the tables and putting all rows in (e.g. storage on the server)

b) efficiency, Will a multi-join on something like 10 tables with all that data be efficient?

c) server maintenance, with all those tables doing an update throughout all the tables, even with PHP is going to be significant.

 

Also I'd like for the users to be able to rate their activities so that groups can be formed based upon how much someone wants to go do an activity.  Also I'd like to be able to have users form lists of people they absolutely don't want do to anything with and form groups based upon their exclusions.

 

Is there an optimal table configuration for my idea here?

[fenway]

Sounds like you should have a table of times (night/morning) and table of items, and then a many-to-many relating them.

[pontifex]

I'm not very up on my database theory, so I don't know exactly what you're talking about, but a quick google search came up with this:

 

http://www.tonymarston.net/php-mysql/many-to-many.html

 

Which I think is what you're talking about.  The problem is that the author is being so obtuse that I can't quite grasp the solution to the problem.  Some of his example code:

 

CREATE TABLE `a` (
  `a_id` varchar(6) NOT NULL default '',
  `a_desc` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`a_id`)
)

CREATE TABLE `b` (
  `b_id` varchar(6) NOT NULL default '',
  `b_desc` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`b_id`)
)

 

Creating two tables to hold our 'many to many' data.  I assume these would be the 'times of day', I mentioned above.  Next the "index" table, contains two foreign keys to the other tables:

 

CREATE TABLE `x` (
  `a_id` varchar(6) NOT NULL default '',
  `b_id` varchar(6) NOT NULL default '',
  PRIMARY KEY  (`a_id`,`b_id`)
)

 

A sample select:

 

SELECT x.a_id, x.b_id, b.b_desc
FROM x, b
WHERE (x.a_id = 'whatever') AND (b.b_id = x.b_id)

 

I think what he's doing here is matching the ID's of A and B together and retrieving all the information where they intersect.  I think I'll have to do some testing, to see how this works.

 

But is this solution you were outlining?  Since I'm going to have quite a few tables to link together, would I need one of these X tables to link each pair or could I get away with a massive index or tiered index?

 

--Pontifex

[fenway]

What I meant was that in principle, each item can have multiple times, and vice versa... so you should link the two outside of their parent tables.

[pontifex]

I think I understand what you're getting at.  Still, I'm not strong on my database theory, so you'll have to bear with me as you explain seemingly obvious things.  I got some help from someone who does much more database interaction than I and he gave me this general plan of attack:

 

location table

(denotes characteristics of the location and vital statistics)

 

person table

(denotes people and their information)

 

event table

(denotes an activity that a person wants to do, contains an ID a date / time, a person_id and location_id)

 

event_person_association table

(links together event_id's and person_id's)

 

Thus you can search the event_person_association table for people that have common events.  This seems like a good idea, though a bit verbose.  *shrug*

 

Intellectually I assume that the event table and the event_person_association table have 'foreign keys' into the other tables to link them together.  But I want to be sure I have the right idea conceptually.  This wasn't terribly helpful as it's explanation assumes some knowledge of databases.  But I think I have the right idea that the foreign keys would link the tables together and provide indexes into other tables as well as cross referencing.

 

This is all well and good, but I'm unable to formulate any successful SQL with the idea in mind.  I've been using MySQL administrator to do most of my table creation work and it's been a nice interface; But it doesn't always work correctly.  I don't know if the SQL database I'm using is configured correctly (I'm using a remote one not configured by me), but the MySQL tools I'm using don't actually generate valid SQL sometimes.  When trying to generate code for the event table:

 

CREATE TABLE `RadRob_sacredweb`.`events` (
  `event_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `date_time` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`event_id`),
  CONSTRAINT `fk_instance_id` FOREIGN KEY `fk_instance_id` ()
    REFERENCES `Instances` ()
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_player_id` FOREIGN KEY `fk_player_id` ()
    REFERENCES `Players` ()
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = InnoDB;

 

I get this, which is not valid SQL.  No errors are reported until an actual commit to the database, which is less than helpful for debugging.  I found this, which implies that the syntax should be:

 

CREATE TABLE `RadRob_sacredweb`.`events` (
  `event_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `date_time` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`event_id`),
  CONSTRAINT `fk_instance_id` FOREIGN KEY `fk_instance_id` ()
    REFERENCES `Instances` ('instance_id')
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_player_id` FOREIGN KEY `fk_player_id` ()
    REFERENCES `Players` ('player_id')
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = InnoDB;

 

Where 'instance_id' and 'player_id' are columns in 'Instances' and 'Players' tables respectively.  Though the examples in the above link have 'indexes' and seem to have different syntax than the things I'm trying to form here.  By the way the creation of invalid SQL by the MySQL Administrator is not limited to these sorts of operations, I have observed several times that the simple creation of normal tables will fail without some tuning by hand.  Usually these sorts of errors are extra parenthesis, but it is enough to convince me that the SQL generation on these products is substandard at best.  I no longer trust it to do the SQL generation for me, except in the simplest of forms.  Which is why I'm trying to puzzle out the syntax here.

 

--Pontifex

[fenway]

To be honest, I don't use InnoDB, so I'm not familiar with the FK constraint syntax.