0

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.

  • "Should user_id be a foreign key": Not for making something faster but, yes, that's a good idea in general. (And as MySQL creates an index on a foreign key, it maybe even speed up some queries.) – sticky bit Aug 2 '18 at 13:37
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.