Reputation: 1127
What is the best way to store data in this scenario:
ACCOUNT table stores id, username, password and a large set of data comprising of multiple rows. Example: multiple sessions stored with date and other information.
What I'm doing now is creating a new table called sessions_id# for each user, but there has to be a more efficient way.
Upvotes: 0
Views: 452
Reputation: 22340
Instead of creating a new table for each user's sessions, create a table SESSION that holds sessions for all users at once. This satisfies a database design principle called "normalisation" that you should read up on. It is more scalable than creating a new table every time a new user signs up.
CREATE TABLE SESSION (
SessionID INT NOT NULL,
AccountID INT NOT NULL,
StartDate DATETIME,
PRIMARY KEY (SessionID),
CONSTRAINT Constr_SESSION_AccountID_fk
FOREIGN KEY SESSION_AccountID_fk REFERENCES ACCOUNT (AccountID)
ON DELETE RESTRICT ON UPDATE RESTRICT
)
Upvotes: 0
Reputation: 1885
You are correct in your assumption that there is a better way. What you need is the "relational" in relational databases.
You need only 1 table for the sessions. This table contains columns for the session data, probably a unique ID (use auto-increment) and most important of all, one field where you store the ID of the user the session belongs to.
This is called a one-to-many relationship, because one user can have many sessions, but each session is coupled with only one user.
If you use MyISAM as the DB engine, you will need to keep track of the IDs manually, but if you choose InnoDB as the engine, you can use foreign keys.
You can read more about foreign keys in the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
Upvotes: 0
Reputation: 42627
You should be able to create a single sessions table that has a foreign key reference back to the account table. The session table would have columns for an identity, then the user's ID, then the session-related data (date, context, etc.)
Upvotes: 2