I have normalised my database to 4NF. How do I now insert values at the same time when records have been split into multiple tables? most of the child tables reference their parent table CUSTOMER column
customer_ID as their foreign keys. The primary key in the parent table is auto-incremented.
There are many tables set up this way, but I thought the following 3 samples would sufficiently paint the picture:
CREATE TABLE customer ( CustomerID INT(11) NOT NULL AUTO_INCREMENT, CusCatID INT(2) NOT NULL DEFAULT 1 COMMENT 'This is supposed to reference the customer catergpries table. But its giving a foreihn key/ index probpem?', `First Name` VARCHAR(50) NOT NULL, LastName VARCHAR(255) NOT NULL, EmailAddress VARCHAR(50) NOT NULL, GENDER BIT(1) NOT NULL, DOB DATE NOT NULL, Cus_Status_ID INT(11) NOT NULL DEFAULT 1, PRIMARY KEY (CustomerID) ) ALTER TABLE customer ADD CONSTRAINT FK_customer_CusCatID FOREIGN KEY (CusCatID) REFERENCES customer_category(CusCategoryID) ON DELETE NO ACTION; ALTER TABLE customer ADD CONSTRAINT FK_customer_Cus_Status_ID FOREIGN KEY (Cus_Status_ID) REFERENCES status(StatusID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE customer_accounts ( Cus_ID INT(11) DEFAULT NULL, AccountNo INT(11) NOT NULL ) ALTER TABLE customer_accounts ADD UNIQUE INDEX AccountNo(AccountNo); ALTER TABLE customer_accounts ADD CONSTRAINT FK_customer_accounts_AccountNo FOREIGN KEY (AccountNo) REFERENCES account(AccountNo) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE customer_accounts ADD CONSTRAINT FK_customer_accounts_Cus_ID FOREIGN KEY (Cus_ID) REFERENCES customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE customer_authentication ( auth_id_cusId INT(11) NOT NULL, Username VARCHAR(255) NOT NULL, Password VARCHAR(255) NOT NULL, PIN INT(6) NOT NULL, PRIMARY KEY (auth_id_cusId) ) ALTER TABLE customer_authentication ADD UNIQUE INDEX Username(Username); ALTER TABLE customer_authentication ADD CONSTRAINT FK_customer_authentication_auth_id_cusId FOREIGN KEY (auth_id_cusId) REFERENCES customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;