Designing a database for user-customized groupings

Question

A user can search for colours and add them to their palette..

So if they write blue, blue will show up and they can add it.

so on with red, yellow.......

But I want, that if they choose "dark colors", then grey black and brown should be added.

Today I´m doing like this

if($color==darkcolor) 

insert black and grey and brown...

Is there any easier way to achieve this. I have hundreds of attributes I want to add and sitting and writing php if, else strings is to much time consuming...

Answer 1

You don't have a specific php/mysql problem here.

Your best bet would be to to add attributes to your colour database

e.g.

Name      Attribute
Green     Natural, Light
Brown     Natural, Dark

The you can simply SELECT Name FROM Colours WHERE Attribute LIKE <Query String>

If there are a limited number of attribute categories then you could specialise the table further by adding column names like "Lightness", "Theme" etc.

Answer 2

I'd set up a database with a few tables.

colours
   id
   name

attributes
   id
   name

colour_attributes
   colour_id
   attribute_id

That way, you can keep track of an unlimited amount of colours and attributes, and tie attributes to colours via the association table colour_attributes.

Answer 3

Add a category table, so that you can keep track of them. Then, add a color_category table so you can have a many-to-many. Like so:

CategoryID    Category
----------------------
         1    primary
         2    dark

ColorID    CategoryID
---------------------
     1              1
     1              2
     2              2

ColorID    Color
-----------------
      1    red  
      2    brown

Next, have a UserPalette table that looks like this:

UserID    ColorID
-----------------
     1          3
     1          4

So, when a user selects dark, you would run this query:

insert into userpalette
select
    $userId as userid,
    c.colorid
from
    category cat
    inner join color_category cc on
        cat.categoryid = cc.categoryid
    inner join colors c on
        cat.colorid = c.colorid
where
    cat.category = '$category'

To grab a user's colors, you'd do this:

select
    c.color
from
    userpalette up
    inner join colors c on
        up.colorid = c.colorid
where
    up.userid = $userid

If you just have a username:

select
    *
from
    users u
    inner join userpalette up on
        u.userid = up.userid
    inner join colors c on
        up.colorid = c.colorid
where
    u.username = '$username'

Obviously, make sure you pipe everything through mysql_real_escape_string() before you put it into a SQL query. Otherwise, you're leaving yourself open for SQL injection attacks.