Bruce ThereIS a ?Custody? table with DCode, VID and the date one was assigned to theother.However, the user wants to beable to see at a glance who?s got what and where.I?m dealing with HIS structure and am notfree at this point to change it. Bestsolution is to get the rule to work! David
David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261 -----Original Message----- From: Bruce A. Chitiea [mailto:[email protected]] Sent: Tuesday, July 30, 2013 06:24 PM To: 'RBASE-L Mailing List' Subject: [RBASE-L] - RE: An unresolved RULES issue from earlier email David: Is there possibly a normalization issue here? To wit: Each VEHICLE may have only one DRIVER at any one point in time. Each DRIVER may have only one VEHICLE at any one point in time. But: Each VEHICLE may have many DRIVERs over time Each DRIVER may have many VEHICLEs over time. So maybe a DRIVER2VEHICLE intermediate table would solve the problem, and provide a record of who drove what when? An ?flag-active? toggle would indicate the assignment and prevent duplicate assignment D2V PK: d2vID FK: drvID FK: vID d2vDateTimeLock d2vDateTimeRelease xFlagActive Or am I missing the point? I?m just sayin? Bruce Chitiea SafeSectors, Inc. eCondoMetrics From: [email protected] [mailto:[email protected]] On Behalf Of David Blocker Sent: Tuesday, July 30, 2013 2:02 PM To: RBASE-L Mailing List Subject: [RBASE-L] - An unresolved RULES issue from earlier email Hi friends I just realized as I temporarily moved forward on my project that an issue I wrote to the list about several weeks ago never was resolved. I?m trying again to see if anyone has an idea for a solution. Here?s the Structure: VEH (vehicles) table has primary key column VID (vehicle ID) Table DRIVERS (people who drive the vehicles) has Foreign key column VID. When a driver is assigned a vehicle, the VID column for that driver?s row is set to the VID value. However, there are two wrinkles here: 1)A Driver may NOT currently be assigned a vehicle, so VID could be NULL 2)If Driver A has a certain vehicle, say VID = 1, Driver B should NOT be allowed to get that vehicle. In other words, NO TWO RECORDS in Drivers should have the same value for VID So the rule I want would: 1)Permit Nulls 2)Forbid two rows in Drivers from having the same value. A rule is needed, because a Unique Key for VID in Drivers cannot be set up unless the column has a NOT NULL constraint. Here?s the rule that I tried based on some emails back and forth with Bill Downall: RULES 'Vehicle ID must be either blank or unique: two drivers cannot + be assigned to the same vehicle' + FOR Drivers FAILS + WHERE VID IS NOT NULL AND VID IN + (SELECT VID FROM DRIVERS d2 WHERE d2.VID = Drivers.VID AND + d2.DCode <> Drivers.DCode) This does NOT work. R:Base DOES allow null values, but it also allows me to set multiple rows to the same VID value. Any ideas? David Blocker David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261
