I have a
users table where there is the basic information of each user:
CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `account_type` varchar(10) NOT NULL, `email` varchar(255) NOT NULL, `password` varchar(500) NOT NULL, `registration_date` timestamp NULL DEFAULT NULL, `account_status` tinyint(2) NOT NULL DEFAULT '0', PRIMARY KEY (`user_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
On the other hand, I have a `user_data' table in which we put every other data:
CREATE TABLE `user_data` ( `data_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `value` text, PRIMARY KEY (`data_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
Basically, the data is stored as a
I fetch the data in the following fashion:
SELECT email, ud_desc.value as description, ud_lang.value as language FROM users JOIN user_data as ud_desc ON ud_desc.user_id = users.user_id AND ud_desc.name = 'description' JOIN user_data as ud_lang ON ud_lang.user_id = users.user_id AND ud_lang.name = 'language'
I made that second table this way because there is a ton of different data for each user, certain depending on their
Also my client keeps adding, removing and changing different kinds of data all the time.
So I needed something quite flexible and this is the best idea I came up with so far, from a developer point of view. Because I fear that this so called "best idea" is just a faint dream and will be too slow on the long term.
Knowing that I might have to perform
MATCH AGAINST queries on the large volume of data that
user_data will hold.
Also, you might take note that I am not very familiar with indexes. But at this point, I have no idea what is the most efficient, given that the data is fetch based on two columns
Should I make both of those columns (
name) indexes? Should
user_id be a foreign key (for the
users table JOIN)?
Is there a way to improve either my database model or my queries?
I thank you all in advance. I am a bit lost at the moment and need some fresh points of view on this matter. Don't hesitate to tell me if you need more details.