Database structure for tags

database-design

I posted a thread on Stack Overflow and one person pointed out that my database structure wasn't the best so now I'm here to ask how to do this in a good and proper way.

Currently I have one table called "users" and in the table I have a lot of userinfo. In "users" I have a column called "tags" were information inside the column look like "#dj #personal #coding" and things like that for every user. Is there any better way to store tags describing the user? I have one column called "tags" were the user have stored tags describing him/her and also a column called "interests" were tags are stored to remember what the user is looking for…

Basically, is there any better way to store these tags for each user?

Best Answer

It makes sense to store tags themselves in a separate table, and add a link table to implement many-to-many relationship between users and tags, for example

users (user_id (PK), name, [other user attributes]);
tags(tag_id(PK), tag_name, [other tag attributes]);
users_tags(user_id (FK to users) ,tag_id (FK to tags), PK(user_id,tag_id));

Depends on your needs, users_tags may have other attributes as well, for instance you might want to add datetime column to store when user added tag (surely, if it has any value to your application)

Related Solutions

Mysql – php/thesql database redesign and migration: changing databases to tables

Instead of Migration to another System, try doing it in place, then mysqldump DATA_ONE and import that mysqldump into the new server.

First, let's create DATA_ONE

DROP DATABASE IF EXISTS DATA_ONE;
CREATE DATABASE DATA_ONE;

Next, Create DATA_ONE tables using the template from database aa123

CREATE TABLE DATA_ONE.personal LIKE aa123.personal;
CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;
CREATE TABLE DATA_ONE.games    LIKE aa123.games;

Now, Create a UserInfo Table

CREATE TABLE DATA_ONE.UserInfo
(
    userid INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(32),
    last_update DATETIME NOT NULL,
    PRIMARY KEY (userid),
    KEY (username)
);
INSERT INTO DATA_ONE.UserInfo (username,last_update)
SELECT database_name,last_update FROM users.userinfo;

Then, add the database_name column (and index it) to the DATA_ONE tables

ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);
ALTER TABLE DATA_ONE.personal ADD userid INT;
ALTER TABLE DATA_ONE.accounts ADD userid INT;
ALTER TABLE DATA_ONE.games    ADD userid INT;
ALTER TABLE DATA_ONE.personal ADD INDEX (username);
ALTER TABLE DATA_ONE.personal ADD INDEX (userid);
ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);
ALTER TABLE DATA_ONE.games    ADD INDEX (userid);

OK, that makes the new DATA_ONE database.

Now comes the adventurous part: Loading all those tables into DATA_ONE.

Run these three queries to formulate the SQL needed for migration

SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;

Here is everything mentioned above shell-scripted and executed:

SQLFILE=/root/MakeDATA_ONE_Database.sql
echo "DROP DATABASE IF EXISTS DATA_ONE;" > ${SQLFILE}
echo "CREATE DATABASE DATA_ONE;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.personal LIKE aa123.personal;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.games    LIKE aa123.games;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.UserInfo (userid INT NOT NULL AUTO_INCREMENT,username VARCHAR(32),last_update DATETIME NOT NULL,PRIMARY KEY (userid),KEY (username));" >> ${SQLFILE}
echo "INSERT INTO DATA_ONE.UserInfo (username,last_update) SELECT database_name,last_update FROM users.userinfo;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (username);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD INDEX (userid);" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -A < ${SQLFILE}

Now open mysql-workbench or phpmyadmin or whatever you browse data with and look through the DATA_ONE database.

If you are satisfied with what is there, just do this:

mysqldump -u... -p... --databases DATA_ONE > /root/DATA_ONE.sql

Move the DATA_ONE.sql file over to the new server and execute the script.

That's it. Pure SQL solution. No PHP. If anything does not work, it is probably because I do not know the layout and current contents of the personal, account, & games tables and made assumptions on data. Please post the following in your question:

SHOW CREATE TABLE aa123.personal\G
SHOW CREATE TABLE aa123.accounts\G
SHOW CREATE TABLE aa123.games\G

Give it a Try !!!

UPDATE 2012-05-23 13:57 EDT

Oh, you have 2500 databases? My above solution should still work.

I do have an alternative suggestion: Try to mysqldump all databases into individual files.

Here is a shell script that will dump all 2500 databases into separate files and gzipped

MYSQL_CONN="-uusername -ppassword"
SQLSTMT="SELECT DISTINCT table_schema FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt
MYSQL_CONN="-uusername -ppassword"
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
COMMIT_COUNT=0
COMMIT_LIMIT=50
for DB in `cat /tmp/ListOfDatabases.txt`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Once you execute this, all databases will be mysqldump'd. For example, the database aa123 will be dumped and gzipped to aa123.sql.gz. This and all the other 2499 mysqldumps can be copied over to the new DB server. Place the dumps in /root/mysqldata on the new DB server. Once they are on the new DB server, you can then unzip all files like this:

cd /root/mysqldata
for X in `ls *.sql.gz` ; do echo ${X} ; gzip -d ${X} ; done

OK this will unzip all the files.

Now, run this to load the SQL scripts into mysql on the new server

cd /root/mysqldata
for X in `ls *.sql` ; do echo ${X} ; mysql -u... -p... -A < ${X} ; done

That's it. No PHP involved. All iteration is handled in the shell script I provided. You can then combine them in the new DB Server as DATA_ONE like I mentioned before.

Give it a Try !!!

Mysql – big db for managing user files and settings

The first path is certainly the more conventional of the two, absent a compelling reason to break the system down into multiple schemas. Something along these lines:

CREATE TABLE user (
  user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_name VARCHAR(50) NOT NULL,
  password VARCHAR(32) NOT NULL,
  status_code INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE user_file (
  file_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_id INT NOT NULL,
  path VARCHAR(2000) NOT NULL,
  INDEX (user_id),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

CREATE TABLE user_settings (
  user_id INT PRIMARY KEY NOT NULL,
  email VARCHAR(150),
  color VARCHAR(10),
  etc VARCHAR(255),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

Best Answer

Related Solutions

Mysql – php/thesql database redesign and migration: changing databases to tables

UPDATE 2012-05-23 13:57 EDT

Mysql – big db for managing user files and settings

Related Question