Design Issue

[theun789]

Hello all,

I am currently working on the design for a new DB, and have run into a wall. In essence, I have a number of primary objects, each with their own table, stored in my database. I want to be able to create notes that can be connected with any of these existing primary objects.

As an example, think of a library with the following tables: Books, Patrons, Loans
Each table has its own ID attribute. I want to be able to create a new "Notes" table so that I can attach notes to any of the existing objects (ie a Particular book, or a particular customer). I imagine it going something like this.

Notes
-NoteID(INT)
-ObjectType(Either varchar ref to object title, ie "Books", or possibly an INT that references another table in which I store the primary object types)
-ObjectID(INT; this will reference the specific objects ID such as BookID)
-Details(Varchar(150))
-etc....

The main point of this would be able to look at all notes together, . Anyone have any idea how I might accomplish this, or if there is a standard DB design used to handle this sort of situation?

Thanks,
Chris

[dw85745]

If I understand the problem, why not put a note field into each table and then create a table of notes. Then have the note field link to the specific record in the note table.

This way you can print all notes, or attach the specific note when pulling up other tables.

[theun789]

That wouldn't quite clear up the problem. I think a better example of the type of object I want to create would be an Action_Requirement object. The object would have attributes such as:

Action_Requirement
-Action_ReqID
-OBject_Type(Again, a reference to the object type, such as "Book")
-ObjectID(INT, in this case the BookID, but could also be PatronID or LoanID)
-Date_Opened(Date)
-Required_Action(VarChar(150), such as "Get book rebound")
-etc...

The big idea I am going for here is to be able to query against this Action_Requirement table, and get all the results, not just those related to one object type. An example of the sort of output I would want is:

Object_Type-----Object_Name---Date_Opened---Action
Book-----------Moby Dick--------9/7/08----------"Get book rebound"
Book-----------Frankenstein------9/12/08---------"Order 2 additional copies"
Patron---------John Smith--------9/13/08---------"Call about overdue books"

Whats most important to me is that the output conform to the above example, ie the end user will see one list of "Action Requirements" with the object pertinent data displayed.

To this end, I would prefer to keep the DB back-end layout as I defined above, with one Action_Requirement table that stores them all. If this is not an option, then I would consider splitting the table, and creating a new object specific "Action Requirement" table for each of the primary objects. This would require a bridge table between the specific object, for instance the "Book" table, and the "Book_Action_Requirements" table. If this is the case, my question changes to something like; How do I aggregate similar SELECT query datasets into one global "Action_Requirements" dataset that contains all "Action_Requirements" for all the ovject types.

Thanks for any assistance,
Chris

[alex_read]

You have few designs available to you here.

1. 1st off you can create a generic action/note/requirement table, then have mapping tables to allow an object such as a book to many actions relationships:
   
   Action
   ActionID
   ActionDescription
   
   BookActionMapping
   BookID
   ActionID
   
   PatronActionMapping
   PatronID
   ActionID
   
   LoanActionMapping
   LoadID
   ActionID
   
   
2. Next up, you could have a generic mapping table also:
   
   Action
   ActionID
   ActionDescription
   
   ObjectActionMapping
   PKColumnID
   TableNameID
   ActionID
   
   ObjectTableName
   TableNameID
   TableName

Personally, I would second the above and split this out into BookAction, PatronAction and LoanAction tables. I realise you are trying to be as normalised as possible, but you have to think of expandability of the database for the future, as well as the ease and speed of SQL coding to access these. Keeping them separated is still a completely valid normalised option.

[FunkyDexter]

The 'correct' way to go would be to implement the separate action tables and then query all of them using a union. This obeys third normal form and will give you the output you require. However, there are rare occasions when breaking third normal form can be worthwhile and this may be one of those times. I say may becasue you should think really carefully before doing so.

I designed a technical document management system years ago where I faced a very similar problem to yours. There were several different types of document which differed enough to merit separate tables. The problem was that any document could reference any other document. That meant that to keep strictly to third normal form I would have had to implement a linking table for every single permutation of document pairs - the number of tables would have been unfeasible. In the end I went with a solution very similar to the one you're suggesting, the references table (which equate to the action table in your scenario) contained type fields which contained the table names of the documents referencing and being referenced. Actually I found this worked very well except you can't implement referential integrity in your table design so you have to handle this programmatically, either in your client or using triggers etc.

Another suggestion would be to implement an inheritance structure: Create a generic 'actionable_objects' table into which you put all the fields that are common to all the different objects which might have an action. You then create child tables for each of the individual types (book, patron etc) which contain the fields which are individual to the type and reference the actionable objects table. The relationship here is actually a 0 to 1 rather than the more standard 1 to many but that still meets third normal form so shouldn't cause any problems. Your actions now reference the actionable objects table rather than the individual child tables. In hindsight this would have been ideal for my scenario as the different document types shared alot of commonality but in your case there may not be enough commonality to make it worth while.

You might, in this instance, also want to investigate object orientated databases because they tend to offer functionality that supports this type of structure. Be warned though, unlike relational DB's there are no standards for OO Db's which introduces lots of problems where portability and communication between applications is concerned.

[kevchadders]

Some good examples.

How i might do it as follows.

Action
ActionID
ActionDescription

Book Table
BookID
ActionID

Patron Table
PatronID
ActionID

Loan Table
LoadID
ActionID

I would create a ActionID in each table, which requires notes to be made.
I would then make that field Data Type of Text.
The reason for making in Text would be so that you can make multiple updates against the same book/patron/loan, without the need of having any more ID's against that item.

For example, each time you add new notes against that book you could datetimestamp those notes with the username also.

Also any additional information you might need to store with the notes (Object_Type/Object_Name/Date_Opened/Action etc) can be stored in the Actions table, which could be display in some way when the user is looking at the notes for that item.

Of course you would need some sort of notes Form/Design to help store all the required information.

Here is a quick mockup of how a book note could look.

[alex_read]

The reason for making in Text would be so that you can make multiple updates against the same book/patron/loan, without the need of having any more ID's against that item.

You can also do that with an UPDATE statement on the foreign table row which the ID field relates to...

[kevchadders]

Yep Alex, good point.

[theun789]

From what I have gathered, it seems like storing all Actions in one table and using a Union with the SELECT query will solve my problems. Thanks to everyone for their help.

Chris

[FunkyDexter]

I think you mean saving them in separate tables don't you? If you store them in one table the union would be redundant. Or am I miss-understanding what you're getting at.