[Solved] How to create a table for a table in phpmyadmin?

Question

The design of your table is going to depend a lot on the requirements you need fulfilled.

In the situation where a member can only ever be assigned to a single room, but rooms can have many members, then the answer that Double H gave is exactly what you’re looking for:

CREATE TABLE members (
 member_id INT,
 member_type VARCHAR,
 member_name VARCHAR,
 member_username VARCHAR,
 member_password VARCHAR,
 room_id INT FOREIGN KEY REFERENCES room(room_id)
)

Now, if a member could participate in more than a single room, and rooms can have more than a single member, you start getting off into other types of relationships (e.g. many-to-many).

The MySQL documentation as well as textbooks (e.g. from the library) can all do really good jobs of explaining the different types of relationships inside a RDBMS (relational database management system). Understanding the logical way tables are joined is a key step before understanding how to physically join them in the database (like, for example, through phpmyadmin).

For implementation details of a many-to-many relationship, there are many resources online including this one by Pinal Dave: Many-to-many Relationships. It basically explains that a third table is necessary to successfully make the relation.

Accepted Answer

The design of your table is going to depend a lot on the requirements you need fulfilled.

In the situation where a member can only ever be assigned to a single room, but rooms can have many members, then the answer that Double H gave is exactly what you’re looking for:

CREATE TABLE members (
 member_id INT,
 member_type VARCHAR,
 member_name VARCHAR,
 member_username VARCHAR,
 member_password VARCHAR,
 room_id INT FOREIGN KEY REFERENCES room(room_id)
)

Now, if a member could participate in more than a single room, and rooms can have more than a single member, you start getting off into other types of relationships (e.g. many-to-many).

The MySQL documentation as well as textbooks (e.g. from the library) can all do really good jobs of explaining the different types of relationships inside a RDBMS (relational database management system). Understanding the logical way tables are joined is a key step before understanding how to physically join them in the database (like, for example, through phpmyadmin).

For implementation details of a many-to-many relationship, there are many resources online including this one by Pinal Dave: Many-to-many Relationships. It basically explains that a third table is necessary to successfully make the relation.