Cascading changes in other tables

[uopdom]

Hi, I'm a database novice and need a few pointers (please).

I've created two tables in MySQL:

CREATE TABLE `Users` (
`Username` VARCHAR( 20 ) NOT NULL ,
`Password` VARCHAR( 18 ) NOT NULL ,
`Secret_Question` VARCHAR( 100 ) NOT NULL ,
`Secret_Answer` VARCHAR( 100 ) NOT NULL,
`First_name` Varchar( 20 ) NOT NULL ,
`Surname` Varchar ( 20 ) NOT NULL,
`Email` VARCHAR ( 20 ) NOT NULL
)

CREATE TABLE `Phonebook` (
`Book_Name` VARCHAR( 30 ) NOT NULL ,
`User_ID` VARCHAR( 20 ) NOT NULL
)

I've created primary keys in both tables Table 'Users' the PK is Username and table Phonebook is 'Book_Name'.

What I've been looking at doing is after inserting a row into "Users", getting the username to cascade into "Phonebook" automatically. I'm not necessarily asking for the source to do it (although that would be nice) just a little guidance on where I can read about it. Thanks!

[griswolf]

This is not something I'd have even thought of trying... That's what transactions are for:

(pseudocode)
  begin transaction
    insert blah into table_1;
    if problem inserting bleh into table_2; then rollback;
  end transaction

Another way to think about this is "How do you know which Book_Name to use when creating a new user?" That decision is a separable concern, so it is sensible to have it in a separate bit of SQL; though it does belong in the same transaction.

[smantscheff]

I'd rather go one step back and ask: What for do you need a value in a table which can be computed by values from other tables? This is violating the normalization design principles. I you can replace the Phonebook table by a view, you should do so.

[griswolf]

I'd rather go one step back and ask: What for do you need a value in a table which can be computed by values from other tables? This is violating the normalization design principles. If you can replace the Phonebook table by a view, you should do so.

I think OP may need to associate a given user with more than one phonebook. If so, then both tables are needed. If it is only possible to have the user in exactly one phonebook, then you are correct: the phonebook should become a column in the Users table and (if useful) a view can be created.