CODEWITHSUNDEEP
sqlmysqldatabase-design
MAX POWER
MAX POWER

Reputation: 5448

MySQL Search Query

I am creating a system to store user stat data as follows:

Table Stats
===========
id    name
-----------
1     height
2     weight
3     eye_colour
4     etc...

Table Stat_Options
==================
id    stat_id    name
1     1          170cm
2     1          172cm
3     1          174cm
4     2          60kg
5     2          65kg

And I store the Ids of the stat_options in the user table as follows:

Table User_Stats
================
id    user_id    height    weight    eye_colour    etc...
---------------------------------------------------------
1     10         3         5         7

The reasoning behind storing the Ids was that the 'name' of the stat_option can always be changed and the user record would not need to be changed.

But looking at this I think it may present a problem, especially when searching. For example if I want to search for all users between two specific heights? One solution that comes to mind is to insert a 'value' field in to the Stat_Options table, but I'm still unsure if that would work. Any suggestions?

Upvotes: 2

Views: 121

Answers (5)

KFleschner
KFleschner

Reputation: 499

From your example, could you get all users with a height between x and y by querying:

Select user_id from User_Stats where User_Status.height between x and y

Upvotes: 0

Danosaure
Danosaure

Reputation: 3655

The problem with this type of design is that you are mixing quantitative values (height, weight) with qualitative values (eye_colour) in your Stat_Options table. If you absolutely want this, break down quantitative and qualitative values into 2 columns.

Table Stat_Options
==================================
id   stat_id   name   value  unit
===  =======   =====  =====  =====
  1        1   170cm    170     cm
  2        1   172cm    172     cm
  3        1   174cm    174     cm
  4        2    60kg     60     kg
  5        2    65kg     65     kg
  6        3   black   NULL  black

Then as other suggested, JOIN tables to get values BETWEEN x AND y.

Upvotes: 1

Luis Alvarado
Luis Alvarado

Reputation: 9386

SELECT user_id FROM User_Stats WHERE height BETWEEN 170 AND 172;

i would guess that one.

But for the structure of your tables i can suggest the following:

  1. There is no need for the Stats table since it can be included with no problem inside the User_Stats Table. That way you would normalise better the usage for the tables.

  2. IF and only IF the values for height and weight can be more than the 3 you have there (For example 180, 183, 192, 200, 110...) then i suggest the same as point 1. To drop the table and included inside the User_Stats table since this value can change and are not really necessary to make a new table just for that. That way you can do searches faster and easily comparing anything you want in a single table.

Upvotes: 0

bAN
bAN

Reputation: 13825

This is not a good idea to do like this.. Use Stat_option to bind dropdownlist for exemple, in case you have a number never not often changing of values..

why Don't you just add a integer field to the User-stats table with the height?

But if you really want you can do:

SELECT * FROM USER_STATS 
INNER JOIN STAT_OPTION 
ON USER_STATS.height in (
 SELECT ID FROM Stat_Options
 WHERE NAME BETWEEN 170 AND 175)

Or something like that, i'm not usual with MySql..

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

IMHO, you should use a lookup table like your Stat_Options only for cases where the attribute has a finite number of values, like eye color. Trying to use a table like this for attributes that (theoretically) have an infinite number of values, like height and weight, will, as you've already guessed, cause major headaches. I'd store those values directly in your User_Stats table instead.

Upvotes: 1

Related Questions