Tom- Perhaps it would help if you post the code attempts you have made so far and myself or someone else on the list can help you?
As the risk of sounding like a jerk, it is fairly unlikely that someone will write your app for you. Give us a starting point to show you have written some code and where you got stuck and we can help you out. If you haven't written anything yet, I would pull up one of the many available one-to-many code tutorials, cut/past one of the examples into your code editor, and rename the tables and columns to match yours. If that doesn't work, post what you did and we can help you get unstuck. -Cameron On Mon, Dec 12, 2011 at 2:53 PM, Tom Small <t...@re-base.net> wrote: > 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:349069 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
