CODEWITHSUNDEEP
mysqldatabase-designdatabase-schema
arboles
arboles

Reputation: 1331

creating a new mysql table with a primary auto incrementing id that is linked to another table?

I want two tables to share a primary auto incrementing id, is this possible? how do i do this? is their anything i need to consider?

the reasons i am doing this, is because it is a better solution than adding groups column to the users table, and also better than creating a completly seperate groups table, because if they share a primary key, i can use the existing posts table for both groups and users. instead of having to create a two distinct posts tables, (group_posts table for group posts. and a user_posts table for user posts.)

existing users table is

id(primary, ai)

username

password

email

my groups table that i want to link to my users table with a shared ai primary key

id(primary, ai, linked to users table id)

group_name

created_by

creation_date

etc.

Upvotes: 0

Views: 90

Answers (1)

Romain
Romain

Reputation: 12809

You should make you schema clearer by doing the following:

  1. Create a table (e.g. people)
    • id, primary key, auto-increment
    • type, tells you if it's a user or a group
  2. Make users and groups primary keys foreign keys on people
    • Insert records in people
    • Obtain the ID that was assigned using LAST_INSERT_ID()
    • Insert in users or groups appropriately, using the ID obtained above

Then you'd reference "people", and not "users" or "groups" in your posts table and so on.

Conceptually, thinking of it in an OO way, it means users and groups both extend people.

Upvotes: 1

Related Questions