Mysql – Database logging users out

Seriously look at third normal form. I would use surrogate keys with the natural keys implemented as unique keys. You will likely find that author belongs in its own authors table. You may find you have a few tables which are quite similar such as user_content_faves, user_author_faves, user_author_shares. This is normal.

Having a single content table with a content_type column may be appropriate. The content column would need to be capable of storing all the content types.

EDIT: For relationship tables I usually name the join table by concatenating the names of the joined tables, abbreviating as necessary. If there multiple relationships between the two tables, I use one of two options:

• Append the purpose of the relationship (as I did above); or
• Add a type/reason code to the relationship (in which case the type is not needed in the relationship name).

I made the assumption that you would want to track who favorited or shared things. It appears that both the producer (author or user) as well as the content items. Therefore you have users favoriting a producer (user_author_faves now user_user_faves), or a product (user_content_faves). Depending on how you do sharing,

• it could be an attribute on the content, or
• a relationship like user_content_shares, where content is share with a particular user. Re-sharing could be problematic, if you track who shared things, and multiple users share the same content to the same user. Un-sharing re-shared items is problematic if you don't track who did the sharing.

You may want to consider (and set policies for):

• tracking/audit information like when something was done (added, favorited, shared, etc).
• whether to do physical or logical deletes.
• if you do logical deletes how to handle refavoriing or sharing something after a logical delete.

When indexing relationship tables I generally have the primary key consisting of the primary keys of the two tables being joined. A second index with the primary keys reversed, or just the primary key which is the second column in the primary key is usually required. If the relationship between two rows can occur more than once, the column(s) used to differentiate the reason/type and/or timing (since date) of the relationship needs to added to the primary key.

This may depend on where you generated the CSV file. If the CSV file was generated on a Windows machine, there could be some character set issues

See https://code.google.com/p/sequel-pro/issues/detail?id=1629

See the following URLs as SequelPro's character set problems are not new

• https://stackoverflow.com/questions/8910528/understanding-mysql-charset-and-collation
• https://stackoverflow.com/questions/11185324/special-characters-get-lost-in-mysql-export-import
• http://forums.mysql.com/read.php?28,284657,284657

If the CSV file was generated on another Mac OSx server, you should not be having this issue.

You may have to resort to setting the default character set to match that CSV file. Sounds weird to here it goes:

Please run this query and you will see something like this:

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql>

You can also see the character set of the database

mysql> show create database mydb\G
*************************** 1. row ***************************
       Database: mydb
Create Database: CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

mysql>

Perhaps you should load another table that has the matching character set:

CREATE TABLE anothertable LIKE mytable;

Change the whole table's character set

ALTER TABLE anothertable CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

or change a column's character set

ALTER TABLE anothertable MODIFY col1 CHAR(50) CHARACTER SET utf8;

Then, have SequalPro load anothertable.

I guess to be less aggressive, just change the column's character set.