extending query

#11

Derek Turner:

Quote:

On Fri, 12 Mar 2010 11:11:02 +0000, Erick T. Barkhuis wrote:

2. A xm2e-record for this combination of memberID and EventID
exists. In that case, the "myReply"-field either contains "Yes, I
will", or "No, I can't". For this list, you're not interested in
these occurrences. Right?

Wrong. There is no myReply field. The idea is to put the eventID in
the noCanDo column instead. That way the members are attached to the
events in one of two ways: either as yes (the old eventID column from
the time when it was a simple many-to-many) or as no (the new noCanDo
column).

But why two fields, when there is only one reply (which contains either
Yes or No)?

--
Erick

#12

Derek Turner wrote:

Quote:

On Fri, 12 Mar 2010 11:11:02 +0000, Erick T. Barkhuis wrote:

Derek Turner:

On Fri, 12 Mar 2010 07:26:36 +0000, Erick T. Barkhuis wrote:

If I understand correctly, you want:

SELECT event.eventID,
UNIX_TIMESTAMP(eventName), // ????
typeName
FROM event
INNER JOIN type ON
type.typeID = event.typeID
LEFT JOIN xm2e ON
event.eventID = xm2e.eventID AND
(xm2e.memberID = $member OR xm2e.noCanDo = $member)
No what I'm trying to do is list the events where eventID is not present
under either eventID or noCanDo against that memberID.
I'm reading this sentence over and over, but it still doesn't make sense
to me.

noCanDo contains eventID's - those of the events the member has said
'no' to.
But, but....

You have events and members. You also have xm2e, which defines what
members are 'attached' to what events. Now, there are several
possibilities:

1. A member is not attached to an event. So, there is no xm2e-record,
which contains both the memberID and the EventID. These are the ones you
want for a given, right?

Right.
2. A xm2e-record for this combination of memberID and EventID exists. In
that case, the "myReply"-field either contains "Yes, I will", or "No, I
can't". For this list, you're not interested in these occurrences.
Right?

Wrong. There is no myReply field. The idea is to put the eventID in the
noCanDo column instead. That way the members are attached to the events
in one of two ways: either as yes (the old eventID column from the time
when it was a simple many-to-many) or as no (the new noCanDo column). The
idea is to be able to nag those members who have not yet replied and
enable them to sign up (or refuse) online. Also, once a week I want to
produce a spreadsheet which lists members down the side, events along the
top and yes, no or blank in the cells. xm2e is really two cross-reference
tables in one: not elegant but it does keep the data all in one place.

What Erick is getting at is correct. You should not have two fields for
one reply.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#13

On Fri, 12 Mar 2010 11:46:13 +0000, Erick T. Barkhuis wrote:

Quote:

But why two fields, when there is only one reply (which contains either
Yes or No)?

Because I thought that was how many-to-many relationships were meant
work. xm2e contains only foreign keys. InnoDB has them set up to cascade.
Delete an event in the event table and all references to it are deleted
from the cross-reference table.

#14

On Fri, 12 Mar 2010 11:43:17 +0000, Erick T. Barkhuis wrote:

Quote:

Derek Turner:

On Fri, 12 Mar 2010 10:37:20 +0000, Erick T. Barkhuis wrote:

Derek Turner:

WHERE eventName >= NOW() // What does a Name have to do with
NOW()?

eventName has type DATETIME. Not having the gift of bi-location we
can't be in two places at once - it's a unique property that I use to
identify the event. Why not eventTime? a suite of scripts allowing
web-based maintenance of the database requires xxxxID in the first
column and xxxxName in the second.

But Derek, if an Event record already has xxxxID to identify it,
why must the name be unique?
And can't there ever be two or more events called "Boston's Choir
In Concert"?

I would expect an Event record to look like this:

ID Name StartDateTime Duration LocationID
TicketPrice 42 Choir Concert 2010-03-10 20:00 240 LOC14 12.50
[or similar]

Absolutely but then my suite of scripts would bork. It relies on every
table having a unique primary index xxxxID and the second column having
xxxxName where xxxx is the same. I don't give the events names at all,
the 'name' is a generated from the date/time, location and type of
event.

Sorry Derek, but that's plain wrong (IMO). Your suite was designed to
have an eventID to identify the record, and an eventName to name that
particular instance. The name need not be an identifier and is not
necessarily unique.

Agreed, but I have applied a unique index to prevent double-bookings! It
is all the identifier I need, all other info can be derived from daughter
tables.

Quote:

The suite wouldn't bork when you name your event properly.

I can assure you it will. I didn't write the suite, by the way. The suite
does NOT require that xxxxName be unique. xxxx is the name of the table.

Quote:

Also, the Location name is not an attribute of Event. And certainly not
part of an identifier. You would want a foreign key LocID, which points
to the LocID of the Locations table. There, you store attributes of
Location (name, max seats, address, etc.)

Type of Event is also a foreign key to the EventType table (and so, it's
not part of the Event name). The EventType table would have a TypeID,
and then attributes like TypeDescription, SecurityAvailableYN, etc.

It is my impression, that you need a sound Data Model first, before
trying to squeeze any functionality in. Your current table
implementation will sooner or (not likely) later give you many problems,
when you try to extend to application with more functions.

All the above are in place.

-- Table structure for table `event`
--

DROP TABLE IF EXISTS `event`;
CREATE TABLE IF NOT EXISTS `event` (
`eventID` int(11) NOT NULL auto_increment,
`eventName` datetime NOT NULL,
`typeID` int(11) NOT NULL,
`venueID` int(11) NOT NULL,
`conductor` varchar(50) NOT NULL default 'NF',
`organist` varchar(50) NOT NULL default 'KF',
`contactName` varchar(50) default NULL,
`contactPhone` varchar(20) default NULL,
`contactEmail` varchar(50) default NULL,
`fee` int(11) default NULL,
`video` tinyint(1) NOT NULL default '0',
`nuptialMass` tinyint(1) NOT NULL default '0',
`dressID` int(11) NOT NULL default '1',
`notes` text,
`musicList` text,
`download1` varchar(50) default NULL,
`download2` varchar(50) default NULL,
`confirmed` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`eventID`),
KEY `typeID` (`typeID`),
KEY `venueID` (`venueID`),
KEY `dressID` (`dressID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=30 ;

#15

Derek Turner:

Quote:

On Fri, 12 Mar 2010 11:46:13 +0000, Erick T. Barkhuis wrote:

But why two fields, when there is only one reply (which contains
either Yes or No)?

Because I thought that was how many-to-many relationships were meant
work. xm2e contains only foreign keys. InnoDB has them set up to
cascade. Delete an event in the event table and all references to it
are deleted from the cross-reference table.

Your xm2e table would have two foreign keys: EventID and MemberID. All
other fields should be attributes with relevant information.
So, if this xm2e-record would define a reply for one member for one
event, there's a field called "theReply", which may contain 'Yes',
'No', or if you like even something like 'Maybe'. Later, you may extend
that table with other relevant attributes, like "willBringPopcorn"
(Y/N).

--
Erick

#16

On Fri, 12 Mar 2010 12:17:40 +0000, Erick T. Barkhuis wrote:

Quote:

Your xm2e table would have two foreign keys: EventID and MemberID. All
other fields should be attributes with relevant information. So, if this
xm2e-record would define a reply for one member for one event, there's a
field called "theReply", which may contain 'Yes', 'No', or if you like
even something like 'Maybe'. Later, you may extend that table with other
relevant attributes, like "willBringPopcorn" (Y/N).

OK is that good practice? Genuine question

from my reading I got the
distinct impression that cross-reference tables (pivot-tables) should
contain ONLY the foreign keys of the two referenced tables. If that is
not the case then I agree that a much better way is to have a reply
field, easier to implement, too. I didn't realise it was 'allowed' which
is why I was buggering about with a two-cross-references-in-one-table
model.

#17

Derek Turner:

Quote:

On Fri, 12 Mar 2010 12:17:40 +0000, Erick T. Barkhuis wrote:

Your xm2e table would have two foreign keys: EventID and MemberID.
All other fields should be attributes with relevant information.
So, if this xm2e-record would define a reply for one member for
one event, there's a field called "theReply", which may contain
'Yes', 'No', or if you like even something like 'Maybe'. Later,
you may extend that table with other relevant attributes, like
"willBringPopcorn" (Y/N).

OK is that good practice? Genuine question

from my reading I got
the distinct impression that cross-reference tables (pivot-tables)
should contain ONLY the foreign keys of the two referenced tables.

Feel free to throw away that source of information. :-)

Your "cross reference table" is actually an entity with a specific
role. It's not just a xm2e-table (which has no semantical meaning), but
each record is a "Reply from a member regarding an event". I would
rather call this table "Replies" instead of "xm2e", because it contains
all information about a reply from a member with respect to an event.
Anything that is relevant to this reply (which is not relevant to that
event only, or to that member only) is stored here. You may want
attributes like
- datetime of reply
- means of reply (phone, mail, online entry, sms)
- willAttendYN
- willBringPopcornYN
- etc.

--
Erick

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

extending query

comp.databases.mysql comp.databases.mysql

Re: extending query - 03-12-2010 , 06:46 AM

Re: extending query - 03-12-2010 , 06:53 AM

Re: extending query - 03-12-2010 , 07:00 AM

Re: extending query - 03-12-2010 , 07:17 AM

Re: extending query - 03-12-2010 , 07:17 AM

Re: extending query - 03-12-2010 , 07:31 AM

Re: extending query - 03-12-2010 , 07:52 AM