Suggestions for table design for search tags

I could use some suggestions for planning my table structure for adding search tags to my database design. Note: I am a new coder.

My site catalogs fantasy gaming miniatures. I have a table which has the names and images of all the miniatures. I want to somehow link search tags to each miniature.

I plan to have different search tags - in example:

  • race search tags (orc, elf, human, dog, skeleton, table)
  • creature search tags (humanoid, undead, goblinoid, animal, giant,furniture)
  • class search tags (fighter, wizard, rogue)
  • environment search tags (nautical, subterranean, aerial)
  • equipment search tags (sword, bow, shield, armor)
  • sex search tags (male, female)

A miniature may have one or more of the above tags associated with it.

I’m unsure of how to best structure this. Currently my thoughts are:

  1. The “miniatures” table will contain 6 columns – 1 for each of the 6 different types of miniatures.
  2. Each column will contain the related search tags, separated by commas.
  3. The tags will be stored in a table for search tags which will just include the tag name, and the tag type.

This seems like the easiest way for me to accomplish this, but I was just wondering what the pro’s recommend?

I am under the impression that search tags were introduced a very long time ago, now seldom used and have been superceded with instant searching.

Google is a prime example of a good search engine with results being instantly visible after entering a couple of characters. This forum uses the same technique.

Investigate Ajax coupled with searching a mysql database for all your data instead of having to click umpteen times to drill down for specific queries.

no, you should have a separate table where there’s one tag per row per mini

2 Likes

Thank you r937. Just to confirm, is this the structure you are suggesting?

table: tags
column 1: id (primary key auto-increment)
column 2: miniature id (unique)
column 3: race name
column 4: creature name
column 5: environment name
column 6: equipment name

sample data:
column 1: 1
column 2: 212 (id from miniatures table)
column 3: red dragon
column 4: dragon,draconic
column 5: aerial
column 6: wings,claws

no, what you have there is first-normal-form-violating 4 tags per mini, extremely difficult to retrofit additional tags into this schema

here’s what you want

CREATE TABLE mini_tags
( mini_id INTEGER NOT NULL 
, tag     VARCHAR(37)
, PRIMARY KEY ( mini_id , tag )
, CONSTRAINT valid_mini
    FOREIGN KEY ( mini_id ) REFERENCES minis ( id )
, CONSTRAINT valid_tag
    FOREIGN KEY ( tag ) REFERENCES tags ( tag )
);

one row per tag per mini

PK is composite, meaning you cannot assign the same tag to any mini more than once

Thank you for that r937. We are really pushing my ability to comprehend this. I’m not at all familiar with the use of constraints and foreign key (more stuff to read up on later).

As far as I can tell, that is only going to build a table of tags:

i.e.
141 Dragon
141 Red Dragon
141 Draconic
141 Wings

I am not familiar with primary key being comprised of 2 different columns… thank you for letting me know about that.

I do want a “type” associated to each tag, I want to list the available search tags associated with each type. (Its very important to me that I do have a type referenced.)

While I was going to have separate search fields for each type, I am now thinking of just having a single input field where the user inputs the search criteria separated by columns.

Underneath that though, I want to let people know what search tags exist that can be typed in. It would look something like this:

Available Search Tags:

Race:
Human, Elf, Drow, Halfling, Dwarf…

Creature:
Humanoid, Goblinoid, Dragon, Animal…

and etc.

Accordingly, for the tags table (tableTags I want to use these 3 columns:

tags_mini_id
tags_name
tags_tagt_id

The tags_tagt_id would reference the unique tag type from my Tag Type Table (only has 1 column named tagt_id (which contains the types: race, creature, etc).

How would I create a table to accomplish this. Here is sort of what I think it could look like:

CREATE TABLE tableTags
( tags_mini_id INTEGER NOT NULL 
, tags_name VARCHAR(37)
, tags_tagt_id 
, PRIMARY KEY (tags_mini_id, tags_name )
, CONSTRAINT valid_mini
    FOREIGN KEY ( tags_mini_id ) REFERENCES minis ( id )
, CONSTRAINT valid_tag
    FOREIGN KEY ( tags_name ) REFERENCES tags ( tags_name )
);

I noticed the first constraint references (id). Is that correct, or should that be tags_mini_id?

Sorry… out a little out of my depth here… trying to learn MySQL, HTML, CSS, PHP, Javascript all at once, and given my learning disabilities, has been challenging to say the least.

in the sample table i gave you, tag is a foreign key to another table, the tags table

this is where you classify what type a tag is, and it also provides you the list of tags available to show to users

Sorry if I appear a little dense in my old age (I sort of am), but I’m having a hard time learning the different elements you have described and relating them to my tables.

The tables I have involved are:

  • tableMinis (primary key is mini_id)
  • tableTags (tags_mini_id will have the value from mini_id; and tags_name will have the name of the tag, and tags_tagt_type which will have the type of tag from the tableTagTypes
  • tableTagTypes has one primary key column tagt_type which records the 6 different tags I have.

Now, I’m not certain what the elements are in your example:

  • CREATE TABLE mini_tags – This is to be the name of my tags table, which in my case is tableTags

  • ( mini_id INTEGER NOT NULL – This would be the id# from the my tableMinis.mini_id, which in my case is tags_mini_id

  • , tag VARCHAR(37) – This would be the name of my tag from tableTags, which is tag_name

  • , PRIMARY KEY ( mini_id , tag ) – This creates a primary key from the combination of 2 columns (tags_mini_id, tags_name)

  • , CONSTRAINT valid_mini – I’m really not sure what is happening here – I guess every constraint needs a unique name assigned to it?

  • FOREIGN KEY ( mini_id ) REFERENCES minis ( id ) – this is referencing my tableMinis, so my code would state: FOREIGN KEY (tags_mini_id) REFERENCES tableMinis (mini_id) – although I’m really not sure??

  • FOREIGN KEY ( tag ) REFERENCES tags ( tag ) – You wrote that 'tag is a foreign key to another table, so I think that is my tableTagTypes table. So my code would be: FOREIGN KEY (tags_tagt_type ) REFERENCES tableTagsType ( tagt_type ) – but again, I’m confused still.

Any additional help you could offer would be greatly appreciated. Sorry, but we really are pushing the limits of my capacity to understand.

you’re almost there

  • tableMinis one row per mini

  • tableMiniTags one row per mini per tag (without tag type)

  • tableTags one row per tag, including FK to tag type

  • tableTagTypes one row per tag type

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.