Help with adding tags to a database design

[richardcb]

I'm creating an Access database where each record has a category field and also a number of tag fields, ranging from 1-5. I'm wondering what the best way is to add these tags with a view to using them in searches later. Ideally I'd like to be able to sort records based on individual tags or combinations of tags but I'm not sure how to go about this.



For example if I have a record format that goes - ITEM NAME / CATEGORY / TAG 1 / TAG 2 /TAG 3 / TAG 4 - I know I can easily search and sort by CATEGORY but what if I have a tag name that may be in TAG 1 position in one record but TAG 3 position in another? Would I have to sort/search each TAG field or is there a way to find a tag name across all the tag fields? Also could I search for records that have, say, just TAG 2 and TAG 4 in them?

I hope this makes some kind of sense. I'm not an expert Access user by any means but I think it could be of great use to the database I'm working on.

Great to find a community dedicated MS Access. I'd be grateful for any help you could give.

[ranman256]

You sort records in the query fields, not form object tags. What do you mean TAG?

[orange]

As ranman says, What is a Tag? It appears you have jumped in to creating tables without a clear description of WHAT your database is about.
What would you say if someone in a line up at McDonalds -who doesn't know you nor database - asked you to describe the database you are creating?

A short (4-6 line) description in plain English would help readers understand WHAT you are trying to do.

When you see fields Tag1, Tag2, Tag3... a red flag goes up suggesting you may not have Normalized your tables.
Getting your tables and relationships designed and set up to support your "business" is key to database.

Good luck with your project.

[richardcb]

As ranman says, What is a Tag? It appears you have jumped in to creating tables without a clear description of WHAT your database is about.
What would you say if someone in a line up at McDonalds -who doesn't know you nor database - asked you to describe the database you are creating?

A short (4-6 line) description in plain English would help readers understand WHAT you are trying to do.

When you see fields Tag1, Tag2, Tag3... a red flag goes up suggesting you may not have Normalized your tables.
Getting your tables and relationships designed and set up to support your "business" is key to database.

Good luck with your project.

Thanks, with a response like that how can I fail?

I'm sorry I did not make myself clear. As I said I am not an expert in Access and would have been happy to try and explain further what I am trying to do but a hostile reply like yours just makes me want to give up and look for an alternative source of information. I had hoped that a community like this might be a little more tolerant of new members who don't yet 'know the ropes'. It seemed like a forum that was designed to help and encourage people in using Access.

Good luck with your tolerance of those seeking help.

[ItsMe]

Post #3 is appropriate. It appears that you are creating tables that are not of a Normalized structure. What you refer to as a Tag is most likely a Foreign Key to us. However, in order to create tables that have the correct structure, you need to understand some basics first. In order to create a query, you need tables first. In order to create a form or report, you need your queries and JOINS defined.

[rpeare]

I don't see anything hostile in any response on this thread. Look at any thread on this message board, whenever we don't understand the original post any experienced person is going to ask for more information so we are sure we're working on the actual problem and not trying to parse the meaning from someone who is unfamiliar with code/database principles. Saying you are unfamiliar with a principle is not a negative.

[richardcb]

I don't see anything hostile in any response on this thread. Look at any thread on this message board, whenever we don't understand the original post any experienced person is going to ask for more information so we are sure we're working on the actual problem and not trying to parse the meaning from someone who is unfamiliar with code/database principles. Saying you are unfamiliar with a principle is not a negative.

I apologise. My reply to the response from orange was a knee-jerk reaction to some comments I felt to be unhelpful and somewhat condescending, not what I was expecting from a support community such as this. I am an administrator to another forum (unrelated to Access) where we would never reply to the first post from a new member in such a way. Clearly I had not presented my problem in a format that could be understood by the experienced users here so let me try to clarify things more.

'Tags' is a term I use to describe a series of sub-categories for each item in my database. So there is a main category describing the function of the item plus a series a sub-categories describing features of the item. At present I am planning to create a field for each 'tag' or sub-category and was worried about having to search each field to select the item based on that feature or features. Very simply I was just wondering if there was a simpler or better way to do this.

That's it. If this is too basic a problem for this community I once again apologise and would be grateful for any advice of where I might go to find the help I need.

[ItsMe]

Perhaps it would be wise to understand the RDBMS side of Access before proceeding any further with your project.
http://www.ntu.edu.sg/home/ehchua/pr...se_Design.html
and
http://rdbms.ca/database/introduction.html

Then expand on queries, and then the begin to research the Application (GUI) side of Access.

[hansendl]

If I understand your post correctly, it sounds like you're referring to a many-to-many relationship (i.e., each item can have multiple tags, and each tag can apply to multiple items). The "tags" you're referring to help categorize your data so you can find it later, right? For example, if my database was tracking books, I might want to tag a particular book on MS Access programming with "non-fiction," "programming," "databases," "Microsoft," etc. A couple of things you want to consider:

1. You shouldn't arbitrarily limit the number of tags for any particular record to a fixed amount. Is there a reason you used 5 tag fields? What happens if only two tags are needed? What happens when you really need 7 tags for a particular item? Also, is the order of your tags important? Does the entry in TAG_1 have more or less significance than the entry in TAG_5?

2. If there is no business reason for having exactly 5 tags, and the order of the tags makes no difference, you really need to move the item tags to a separate table and create a many-to-many relationship. To do this, first create a new table called Tags that will house nothing but the individuals tags (for all items). This table would have only one or two fields. I recommend an autonumber primary key field ("TagID") and a text field where the name of the tag is stored ("Tag"), but you could eliminate the autonumber field and just go with the text field as your primary key (if you go this route, ensure you have the Cascade Update property selected in the table relationships). You will then create a second table to link your items with their tags. I would call this table ItemTags, and it would have the following fields: ItemID (i.e., to hold primary key values from your Items table), and TagID (i.e., to hold primary key values from your Tags table). The primary key for the ItemTags table will include both ItemID and TagID.

By structuring your tables like this, your database will be able to manage any number of tags for each item, and searching will be much easier. If you're not familiar with many-to-many relationships and data table normalization, recommend you read up on it in the links provided by ItsMe.

Good luck!

[rpeare]

What you likely want is a more normalized structure (if you google database normalization you will find a metric ton of examples/explanations)

But in short if you have a common list of categories and a common list of 'tags' (NOTE be careful about using TAG as a field name or as an explanation because it's a reserved word/property within access and may cause you some programming issues) what you want is probably something like:

tblItem
ItemID  ItemDesc ----> other item related fields
1       Item AAA
2       Item BBB

tblCategory
CatID  CategoryName ----> other category fields
1      Category AA
2      Category BB
3      Category CC

if an item can have multiple categories you would then want a junction table

tblItemCategory
IC_ID  ItemID  CatID
1      1       1
2      1       2
3      2       2 
4      2       3

Where item AAA belongs to categories AA and BB, and item BBB belongs to categories BB and CC.

If an item belongs to only one category and it will not change you can consider putting it into your tblItem table.

The same methodology should apply to your 'tag' items
a table listing all the possible tags then a junction table that would either associate the tags to a category (if there are multiple categories per item) or to the item (if an item has one and only one category).

[richardcb]

If I understand your post correctly, it sounds like you're referring to a many-to-many relationship (i.e., each item can have multiple tags, and each tag can apply to multiple items). The "tags" you're referring to help categorize your data so you can find it later, right? For example, if my database was tracking books, I might want to tag a particular book on MS Access programming with "non-fiction," "programming," "databases," "Microsoft," etc. A couple of things you want to consider:

1. You shouldn't arbitrarily limit the number of tags for any particular record to a fixed amount. Is there a reason you used 5 tag fields? What happens if only two tags are needed? What happens when you really need 7 tags for a particular item? Also, is the order of your tags important? Does the entry in TAG_1 have more or less significance than the entry in TAG_5?

2. If there is no business reason for having exactly 5 tags, and the order of the tags makes no difference, you really need to move the item tags to a separate table and create a many-to-many relationship. To do this, first create a new table called Tags that will house nothing but the individuals tags (for all items). This table would have only one or two fields. I recommend an autonumber primary key field ("TagID") and a text field where the name of the tag is stored ("Tag"), but you could eliminate the autonumber field and just go with the text field as your primary key (if you go this route, ensure you have the Cascade Update property selected in the table relationships). You will then create a second table to link your items with their tags. I would call this table ItemTags, and it would have the following fields: ItemID (i.e., to hold primary key values from your Items table), and TagID (i.e., to hold primary key values from your Tags table). The primary key for the ItemTags table will include both ItemID and TagID.

By structuring your tables like this, your database will be able to manage any number of tags for each item, and searching will be much easier. If you're not familiar with many-to-many relationships and data table normalization, recommend you read up on it in the links provided by ItsMe.

Good luck!

Thank you, hansendl, this is is precisely the sort of information I was looking for. You identified what I am trying to to do exactly and are right in assuming that the number of tags is variable and could be expanded on at a later date. I will try and incorporate your suggestions in my database design - once I get my head round many-to-many relationships!

I appreciate your help and understanding.

[richardcb]

What you likely want is a more normalized structure (if you google database normalization you will find a metric ton of examples/explanations)

But in short if you have a common list of categories and a common list of 'tags' (NOTE be careful about using TAG as a field name or as an explanation because it's a reserved word/property within access and may cause you some programming issues) what you want is probably something like:

tblItem
ItemID  ItemDesc ----> other item related fields
1       Item AAA
2       Item BBB

tblCategory
CatID  CategoryName ----> other category fields
1      Category AA
2      Category BB
3      Category CC

if an item can have multiple categories you would then want a junction table

tblItemCategory
IC_ID  ItemID  CatID
1      1       1
2      1       2
3      2       2 
4      2       3

Where item AAA belongs to categories AA and BB, and item BBB belongs to categories BB and CC.

If an item belongs to only one category and it will not change you can consider putting it into your tblItem table.

The same methodology should apply to your 'tag' items
a table listing all the possible tags then a junction table that would either associate the tags to a category (if there are multiple categories per item) or to the item (if an item has one and only one category).

Thank you, rpeare. Excellent information and a good tip about using the word 'tag'. I will make sure I don't use it as a field name in my database.