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?
My solution:
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"
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.
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.
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:
http://en.wikipedia.org/wiki/Database_normalization
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.
Member
53 Points
139 Posts
I would like some opinions on this solution
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?
My solution:
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!
Niklas
Participant
998 Points
339 Posts
Re: I would like some opinions on this solution
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.
example:
area1 - 1
area2 - 2
area3 - 4
area4 - 8
area5 - 16
area6 - 32
... etc
A user with a value 26 in the "materials" field is then part off area5, area4 and area1.
Contributor
2103 Points
794 Posts
Re: I would like some opinions on this solution
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.
Materials:
1 - Material A
2 - Material B
3 - Material C
Areas:
1 - Area A
2 - Area B
3 - Area C
MaterialAreas:
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:
http://en.wikipedia.org/wiki/Junction_table
Member
53 Points
139 Posts
Re: I would like some opinions on this solution
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!
Contributor
2103 Points
794 Posts
Re: I would like some opinions on this solution
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:
http://en.wikipedia.org/wiki/Database_normalization
Member
53 Points
139 Posts
Re: I would like some opinions on this solution
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 =/