Abdulla
Abdulla

Reputation: 1127

MySQL Storing Unlimited Data within Row

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

Answers (3)

Hammerite
Hammerite

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

Adrian Schmidt
Adrian Schmidt

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

Joe
Joe

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

Related Questions