Tagging and Flagging System

[Aoirthoir]

Here is the edited text of a post I made to our company's programming forum:

I want to set up a method of doing select lists. Of marking particular records. This way the select list can be given a title of any sort. And a person would see ALL records OR records matching those that had been previously selected. Select Lists should be savable for future reference at any time.

I envision a two table system basically....generically connecting any table to the list. So each table is going to need a TableSerialNumber in addition to the RecordID. There thus should be a TableSerialNumber table with the info on each table, what it is for (connecting to a field TableSerialNumber table?). I have to begin mapping this out because there are LOTS of uses for select lists.

Select Lists should also be able to automatically select a group of records. For instance I want to tag Customers to call next month. I can tag them individually. Or I could tag all the ones I took orders from this month. Or I could tag all the ones in ohio. Or I could do all three. Using a SELECT in SQL (Select Claimants where State = "Ohio") would give me all of those. Then I could click a button to tag ALL of them immediately. etc etc...

Ok clearly this needs some thinking but the basic idea is in my head.

[Aoirthoir]

This is similar to the notes and the history methods Ive spoken about on other posts. Since every table will have a SerialNumber field then it shall be easy to link a generic table like a flag or a note table to any table. The serial number field will have the same value in each and every record...

There might have to be a class field? or something...see my notes posts about an owner field.. So that I can see tags, that while applying to for instance a particular order, I could also see all tags for a customer, including ones that were originally applied to an order, or any other table related to that customer table...

Though as I said if there is some kind of a CLASS serial number field then that would be built in also and thus be able to be generic....

As I said above the idea needs some thinking but the basics are there..the hardest part is going to be able to have relationships dynamically and generically assigned that can relate UPWARDS to the parent (owner) table....

Also another thought is...if a part of the table description can be used in a relationship then there would be no need for a serial number field in the main table (perhaps the comments in the table)...

However I think just having a serial number field works better.

[shannah]

I'm not sure if this is exactly what you are doing, but I have created a number of relationships similar to the following.

CREATE TABLE Articles (
ArticleID INT(11) auto_increment NOT NULL,
ArticleTitle VARCHAR(128) NOT NULL,
ArticleContent TEXT,
PRIMARY KEY (ArticleID))

CREATE TABLE Stories (
StoryID INT(11) auto_increment NOT NULL,
StoryTitle VARCHAR(128) NOT NULL,
StoryContent TEXT,
PRIMARY KEY (StoryID))

CREATE TABLE Comments (
CommentID INT(11) auto_increment NOT NULL,
SubjectTable ENUM('Articles','Stories') NOT NULL,
SubjectID INT(11) NOT NULL,
PRIMARY KEY (CommentID))

Where the Articles table has relationship defined by:
[Comments]
__sql__ = "select * from Comments where SubjectTable='Articles' and SubjectID='$ArticleID'"

and the Stories table has relationship:
[Comments]
__sql__ = "select * from Comments where SubjectTable='Stories' and SubjectID='$StoryID'"

So that you can add comments to either Stories or Articles.

Is this similar to the kind of thing you are trying to do?

[Aoirthoir]

Yes very very similar. Really the only difference is, that for instance with our marking of records (going to callit marking rather than tag so as not to confuse terms like html tags).. so

our marking of records will be a generic marking table. With a few certain fields (user who marked, date of marking, maybe other things like date finished..what have you.. a field to know which table this is marking and a field to know which record of that field is marked. thus all my tables will be able to be marked if I want...

the same goes for notes...one table.. nearly identical to the marking table, (or marking tables...say a header and line items) so this one system for notes would allow someone to apply notes to anything...

Ah and looking at your code...the only real difference would be this:

__sql__ = "select * from Comments where SubjectTable='Articles' and SubjectID='$ArticleID'"

__sql__ = "select * from Comments where SubjectTable='Stories' and SubjectID='$StoryID'"

both become:

__sql__ = "select * from Notes where Notes.CalledSerialID=ID_Serial and Notes.CalledRecordID=ID_Record"

Thus you see it can apply to any table. As long as that table has a field called ID_Serial and that ID_Serial is the same in all rows for that table.

(Currently Ive decided to use ID_ prefixes for fields in tables so that I know that those fields belong TO the current table. The "CalledSerial..etc" field might end up being named something else. Perhaps RELATED_Serial RELATED_Record.. The main thing is, that I know that that value in that field comes from another table. Because of course my Notes table and my Marking table will have its own ID_Serial and ID_Record.

[shannah]

It seems as though you're trying to simulate "global" fields here where the database doesn't support them. I'm not sure what the advantage of this would be. If all of the rows in the table have the same value then why not just use existing data about the table (e.g. table name). Albeit, I may not completely understand the requirements.

-Steve

[Aoirthoir]

Table name in itself would be fine. But I wish this to be fully generic. Also there are going to be instances where we have tables with the same name but in different databases. In the event that I would ever need to merge databases, if the records were based on table names then I have to do some playing around to make it all work.

Even then though its just a couple of SQL statements to change the value of all notes that reference the table customer to CLIENT. So I am not opposed to using the table name. However I want eventually for this to be some kind of class or class like thing. So really all I have to do if I want to create or read a note (or mark or whatever) is call the class and it automatically references the table I am calling from. So I am trying to make this as generic as possible right from the start.