Hi Cameron I am not having any errors as I have a limited understanding and have followed the adobe tutorials...
Although I am having trouble understanding how to create a relationship between more than one table. I have linked the core table 'Clients' to the sub-table 'Whitelabel' (one-to-one), 'Users' (one-to-many) and 'Users' to Userrole' (one-to-many). My issue is how to link the foreign keys in the 'Users' to the 'Userroles' Appreciate any help on this matter! Mysql code: CREATE TABLE clients ( clientid INTEGER(11) NOT NULL AUTO_INCREMENT, clientname VARCHAR(50) NOT NULL, isactive VARCHAR(1) NOT NULL, departmentname VARCHAR(50) NOT NULL, departmentaddress TEXT NOT NULL, contactname VARCHAR(50) NOT NULL, contactemail VARCHAR(50) NOT NULL, contacttelephone VARCHAR(50) NOT NULL, accounttype VARCHAR(50) NOT NULL, startdate DATE NOT NULL, expirydate DATE NOT NULL, paymentmethod TEXT NOT NULL, notes TEXT, PRIMARY KEY (clientid), UNIQUE (clientname) ) ENGINE=InnoDB; CREATE TABLE whitelabel ( whitelabelid INTEGER(11) NOT NULL, whitelabel VARCHAR(1) NOT NULL DEFAULT 0, logo VARCHAR(50) NOT NULL, topbar VARCHAR(50) NOT NULL, loginpagehtml TEXT NOT NULL, whitelabelemail VARCHAR(50), whitelabeltelephone VARCHAR(50), whitelabelwebsite VARCHAR(50), whitelabeladdress TEXT, PRIMARY KEY (clientid), FOREIGN KEY (clientid) REFERENCES clients (clientid) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE userrole ( userroleid INTEGER(11) NOT NULL AUTO_INCREMENT, rolename VARCHAR(50) NOT NULL, PRIMARY KEY (userroleid), UNIQUE (rolename) ) ENGINE=InnoDB; CREATE TABLE users ( userid INTEGER(11) NOT NULL AUTO_INCREMENT, email VARCHAR(50) NOT NULL, password VARCHAR(255) NOT NULL, title VARCHAR(5) NOT NULL, firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50) NOT NULL, position VARCHAR(50) NOT NULL, isactive VARCHAR(1) NOT NULL, userroleid INTEGER(11) NOT NULL, showassistance VARCHAR(1) NOT NULL DEFAULT 1, failedloginattempts VARCHAR(1) NOT NULL DEFAULT 0, passwordexpires DATETIME NOT NULL, PRIMARY KEY (userid), UNIQUE (email), UNIQUE (title,firstname,lastname), FOREIGN KEY (userid) REFERENCES clients (clientid) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (userid) REFERENCES userrole (userroleid) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (userroleid) REFERENCES userrole (userroleid) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349068 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
