What sort of database design would I need to use in case I wanted users to save tags, and be able to call already used tags?

Question

I'm trying to implement a feature similar to StackOverflow's tag feature. That a user can create a new tag, or by typing pull up a list of similar tags already created.

This is such a wonderful feature on this site and I find it sad that most sites do not have something like this. It's both robust, and yet very very flexible and best of all: driven by the community.

So I have these two tables:

Company
    id
    email
    name
    companySize
    countryOfOrigin
    industryid

Industry
    id
    description

Every time a user writes a new tag, I want to create one with a unique ID, and also be able to search for existing tags.

Will this database design allow for an easy and efficient implementation of this feature?

If not, please give a little guidance. :)

Answer 1

Whilst there's not a tremendous amount of information to go on, what you've listed should be fine. (The 'tag' being the 'description' field in the industry table, etc.)

As you might imagine, all of the real work is done outside of SQL, where you'll need to...

1. (Potentially) add new tag(s) that don't yet exist.
2. Associate the industry with the supplied tag(s).
3. (Potentially) prune previously used tags that may no longer be in use.

...every time you edit an industry.

That said, the key limitation of your proposed setup is that each company can only belong to a single industry. (i.e.: It can only have a single industry tag associated with it.)

As such, you might want to consider a schema along the lines of...

Company
    id
    ...
    countryOfOrigin

Industries
    id
    description

CompanyIndustriesLookup
    companyID
    industryID

...which would let you associate multiple industries/tags with a given company.

Update...

For example, under this setup, to get all of the tags associated with company ID 1, you'd use...

SELECT Industries.description FROM (CompanyIndustriesLookup, Industries)
WHERE companyID=1 AND industryID=Industries.ID
ORDER BY Industries.description ASC;

On a similar basis, to get all companies tagged with an industry of "testing", you'd use...

SELECT Company.name FROM (Company, Industries, CompanyIndustriesLookup)
WHERE Company.id=CompanyIndustriesLookup.companyID
AND Industries.id=CompanyIndustriesLookup.industryID
AND Industries.description="testing"
ORDER BY Company.name ASC

Answer 2

A very easy (if somewhat suboptimal, but it often does not matter) solution to use tags is to not have tag ids at all. So, you have:

Items
    ItemId
    Name
    Description
    ...

ItemTag
    ItemId 
    Tag

Adding a tag to an item is just adding the tuple to the ItemTag table, whether the tag already exists or not. And you don't have to do any bookkeeping on removing tags either. Just keep an index on ItemTag.Tag, to be able to quickly display all unique tags.