|
|
Posted by --CELKO-- on 11/30/06 14:52
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Guessing at what you meant to post, and using data
element names that make sense, does your schema look like this?
CREATE TABLE Appointments
(appt_id INTEGER NOT NULL PRIMARY KEY,
appt_date DATETIME NOT NULL,
...);
CREATE TABLE Attendees
(attendee_id INTEGER NOT NULL PRIMARY KEY,
attendee_name VARCHAR(20) NOT NULL,
..);
CREATE TABLE Attendance
(appt_id INTEGER NOT NULL
REFERENCES Appointments (appt_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
attendee_id INTEGER NOT NULL
REFERENCES Attendees (attendee_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (appt_id, attendee_id));
1) You never use "-key" as a suffix. That tells us HOW the data
element is used and not WHAT the data element is. Likewise, you never
put silly prefixes that tell WHERE there is an appearance or what the
data type used is.
2) The name "entity" is too vague to be data element name. Do you
also use "thingie" or "Whatyamacallit" in your data dictionary?
Use particular names, not vague ones. If you cannot thik of a good
name, then you do not have a clear idea of what you are doing yet.
3) A table models a set of like entities and therefore should have a
collective or plural name. Unless they really do model one thing.
4) The correct term is a relationship table, in this case a
many-to-many relationship. There is not such thing as an intersection
or association table.
5) Notice that you do most of your work with DRI actions, not in code.
The idea of a RDBMS is that it keeps data integrity for you.
>> My task is to delete all appointments and related Attendance rows where the number of attendees is one or less. <<
DELETE FROM Appointments
WHERE appt_id
IN (SELECT A1.appt_id
FROM Attendance AS A1
GROUP BY A1.appt_id
HAVING COUNT(*) <= 1);
The DRI makes < 1 redundant, but it preserves what you posted. The DRI
actions will re-arrange the Attendance as people or appointments drop
out.
Navigation:
[Reply to this message]
|