simple database problem
- Started
- Last post
- 4 Responses
- CAJTBr
okay, say for example you have a database of categorised links. how would /you/ make it capable of containing links that fit into more than one of the categories?
i come across this type of problem all the time, and i'm never satisfied with my solutions. here are the methods i've tried in the past (illustrated with the same example):
have one field for 'categories' within the links table, and work on an indexOf type basis when searching for things that fit into that category.
have multiple yes/no categories in the links table (eg a 'photography' field, a 'books' field).
have a 'categories' table and an intermediary table with link_id and cat_id as the fields.
does anyone have a better method than these?
- AlphaDK0
The cleanest way to do this would be to follow the last example that you have listed.
I would suggest using three tables and simply joining them together and grouping by your desired output column.
You could use REGEXP and SUBSTR and build a big nasty query based on a character delaminated string held in a single table column, but again, that would be nasty.
- kappa0
might help if you explain why those methods don't work...
with normalization in mind, the most common way would be to use multiple tables, one for links, one for category names, and the intermediary table, like you said.
This is a really simple DB structure, what sort of situation makes it unsatisfactory?
- protoculture0
have to agree with kappa. for the highest degree of db normalization (which is usually what we want) you should use a bridging table.
- CAJTBr0
thanks for the responses.
all of the methods do work. i just wanted to check to see if there was some better method out there that i didn't know about, and from the replies so far i guess not.
regarding the intermediary table method. yes, with just three tables it is a simple structure. but in some circumstances, it just feels like it gets over-complex for the simplicity of the task. eg, i want to catalog a set of photos in a database:
i have a photograph which i've taken. taking up the foreground is a clump of mushrooms, then a landscape scene, leading to mountains in the background. i used a polarizer and a nd grad filter. i used a tripod, some flash on the mushrooms, a remote switch and had the mirror locked up. it was a commercial shot for a magazine, a calender and a photo library.
in order to make a db that could handle this i need:
a 'photo' table
then something like the following tables-
type: still life, portrait, architecture, nature
location: landscape, industrial, indoor, mountains
filters: polarizer, nd grad, nd, red
extras: flash, tripod, reflector, remote switch, mirror lock
purpose: personal, magazine, photo library, calender
this photo would come under: still life, nature, landscape, mountains, polarizer, nd grad, etc. etc.
so including the intermediates, i now have the following tables:
photo
type
location
filters
extras
purpose
photo_type
photo_location
photo_filters
photo_extras
photo_purposeit's just an example problem, but doesn't all of that (and all the extra coding/thought that goes along with administering that) seem like too much for such a simple task?