Last post Sep 08, 2010 09:56 AM by zalkin
Sep 08, 2010 04:55 AM|zalkin|LINK
I hope this is the right forum for my question...
This is a short description of a specific part of my problem:
I'm going to create a portal where artists can upload their art. I need them to be able to choose between 1 to 3 areas in which they mainly work. If they can't find their area from the given options there is a free text field to fill in.
How do you solve this with an SQL database in a generic way?
I'd prefer to have a user table and an area table. It would be easy to add and remove areas from the table as they come up. I wouldn't want the list of areas hard coded some where. I would store the areas in the user table in one row called "material", with
a comma separated list.
Some how this solution seem so common that a more general solution could be available.
Like: "One row in a table need to store several rows from another table"
Is there a good way to tackle this problem?
Thanks in advance!
Sep 08, 2010 06:41 AM|toas1|LINK
A comma separated list leave you with a text field and that dont feel like a good solution to me. I would rather add each new area in the area table with a unique bit and then create the "materials" in the user table as a tinyint. That way you could use
simple bit wise logic to determine what areas each user are in.
area1 - 1
area2 - 2
area3 - 4
area4 - 8
area5 - 16
area6 - 32
A user with a value 26 in the "materials" field is then part off area5, area4 and area1.
Sep 08, 2010 07:31 AM|gabriel.lozano-moran|LINK
This is usually solved by adding a junction table. So you would have 3 tables, a Materials table, an Areas table and a MaterialAreas table.
1 - Material A
2 - Material B
3 - Material C
1 - Area A
2 - Area B
3 - Area C
1 - 1 => Material A - Area A
1 - 2 => Material A - Area B
2 - 1 => Material B - Area A
3 - 2 => Material C - Area B
Read this on wikipedia:
Sep 08, 2010 08:09 AM|zalkin|LINK
This would work perfect if it wasn't for the possibility to fill in a free text field with an area that isn't represented already. I could use this solution (or toas1's above) for every area I can come up with, but how would I combine that with the areas
that the users supply? It feels wrong to add a new area to the Areas-table as they are entered by my users.
Thank you both for helping me!
Sep 08, 2010 08:18 AM|gabriel.lozano-moran|LINK
Why would that be strange? If an area is being entered you first look up if the area already exists and if it is a new area you add it to the area table otherwise you end up with a lot of duplicate areas in the Materials table if you have the users enter
the Areas as free text.
Take a look dat Database Normalization:
Sep 08, 2010 09:56 AM|zalkin|LINK
Because if a user wanted to enter a new area, e.g. sculpture, but misspelled it, that would go down as a new record.
If I do use this solution I guess I could create a "manage areas" page where admins can delete and update these records in the database. But it seems it could generate a lot of extra work rather quickly, for the admins.
What if a user wanted to add two new areas?
Maybe this is the only way though =/