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 name => value
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 users.account_type.
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 LIKE and 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 user_data.user_id and user_data.name.
Should I make both of those columns (user_id and 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.
