[SOLVED]Database efficiency question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

[SOLVED]Database efficiency question

Post by rubberjohn »

Hi,

I have three tables (many-to-many):
USER
USER_TAG_LINK
TAG

In the USER table the user specifies a field of interest and then in the TAGS table they specify a set of descriptive tags.

What I want to do is when the user is entering tags, present the user with a jump menu that contains tags that other users in the same field of interest have already submitted.

In terms of database / server load would it be best to also enter the field of interest alongside the tags in the TAG table (as well as the USER table) as an extra field or remove the field of interest into a separate table - give it an id_number and store this in the USER and the TAG table instead?

Or would it not make a difference?

Does all that make sense?

Thanks in advance.

jb
Last edited by rubberjohn on Tue Mar 07, 2006 4:05 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

how do the interest(s) relate to the tags? Can a user have more than one interest stored? Does each interest connect to the tag?
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Post by rubberjohn »

hi feyd cheers for replying...again

The tags are related to the field of interest. A user will only have one interest.

The field of interest is used as a higher level description whereas the tags are used for finer descriptions. For example say you had an interest in photography - tags for this could be portrait, black and white, film etc. So i cant just list all the tags because they wouldn't apply to someone with an interest in another field.

I want to provide a jump menu to show tags that other users (with the same field of interest) have submitted so that if the current user wants to use the tag as well they just select it from the jumpmenu / list.

I think thats made it a bit clearer.

rj
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

table for users, interests, and tags sounds about it. (linking tables across too, of course)

But that only denotes a single tier of children to interests. In the future, if you'd want to create a hierarchy, a different setup may be in order. Have a look through the recent threads involving hierarchical structures for details there.
rubberjohn
Forum Contributor
Posts: 193
Joined: Fri Feb 25, 2005 4:03 am

Post by rubberjohn »

thanks feyd i'll look into that
Post Reply