CODEWITHSUNDEEP
mysqlsql
Dan
Dan

Reputation: 12096

MySQL auto insert a row into table2 on an insert to table1

Is it possible in MySQL to insert a row into table2 automatically when a row is inserted into table1?

Say I have the following basic structure:

User Table

id           int(8)       - primary key       - auto increment
username     varchar(20)

UserPrivacy Table - userprivacy.userId is a foreign key of user.id

id           int(8)       - primary key       - auto increment
userId       int(8)       - foreign key

Could I get a row inserted into User Privacy Table with default values but the corresponding user.id when a ran a query such as INSERT INTO user (username) VALUES ('Bob');

Is there a way other than triggers or would it be best to just run two queries whenever a user is inserted into user?

Secondly is it a waste of storage to have a separate userprivacy table if it's storing a value for every user? could be more efficient to place it in the user table? but I'm trying to make that as light as possible ..

there are more columns in the user table I'm just keeping it simple for this example

Upvotes: 2

Views: 1979

Answers (2)

Jasonw
Jasonw

Reputation: 5064

Is there a way other than triggers or would it be best to just run two queries whenever a user is inserted into user?

If inserting record into your data store is through your created UI such as through web interface or native app, you probably should have a library that can call to a function to insert and in that function, you have two separate calls to the mysql insert. The drawback is that, if user insert the record via mysql command line, it should be two calls individually. Then again, you are asking another way and so this is what I suggest. My suggestion also encourage users to use the gui that you created.

Upvotes: 0

Marcus Adams
Marcus Adams

Reputation: 53830

You would require triggers in order to "automatically" insert the record into a separate table.

Inserting the record into UserPrivacy with a separate query would be the most common way of implementing this.

If it's a one-to-one relationship, putting it in the same table allows more simple queries (no join required).

Decisions, Decisions ...

With a one-to-(zero to one) relationship, there are more factors to consider.

If the UserPrivacy table is large, then it might make sense to put it in a separate table to save space. This would also be more normalized.

If you were often querying something like "Give me all the Users that don't have PrivacyData", then it might make sense to put it in a separate table. Since indexes don't include NULL values (by default), it would be faster to do a JOIN to a separate table. Of course, the work around is to use a value other than NULL to represent "no privacy settings", but low cardinality also negatively affects performance. Separate tables would be best for this case.

Also, if the privacy data was updated frequently, but not the User data, separate tables would prevent row locks on the User table, and updates are faster on smaller tables, which might improve performance.

If you often needed the UserPrivacy data without the UserData, or vice versa, you might want to separate them.

Still, this might be premature optimization. You might just want to separate them if they better match your models. Consider the simplicity of keeping it in the same table versus the issues of performance, size, and readability.

If the relationship was one-to-(zero to many), you would obviously want a separate table, but for one-to-(zero to one), it's optional.

Finally...

Don't be afraid to separate them, as long as there's a reason.

Upvotes: 2

Related Questions