Dynamic Relationships (Notes & Flags attached to any table)

A place for users and developers of the Xataface to discuss and receive support.

Postby Aoirthoir » Mon Jul 10, 2006 3:06 pm

An idea I am working on is Dynamic Tables. Relationships that can have one table related to many different tables. An example of this is a Notes table. I might want notes for Customers, notes for vendors and so on. I could of course just create a memo field in that particular table. However it gets more entrenched in our application.

Consider customers, currently we have a system where we have a notes file for the particular customer. When an order is taken, a note is written. But the notes are a seperate thing entirely. So if we ever need to reference that note they have to search through, by sight, the entire notes file. They cannot just go to an order and review the notes for that order (This is DOS based..) (Actually they cannot even review the order because this system is so badly written, when you create an invoice, the order is DELETED...)

Now what my employer wants is the ability to create a note on every order. Simple enough. We also want notes on every customer. Still simple. But our customers are patients and we will have doctor information for each. So we want a relational note for this doctor for this patient. We also want notes for the Diagnosis. Notes for the insurance information for the customer etc. We have to have these seperate abilities because different people deal with different aspects of the customer. Thus they have to be able to reference notes they need without the clutter of other notes not related to their task.

Now it goes a step further. We want to be able to view all the notes for a particular patient if we choose. Usually sorted by date, but with the ability to sort by the module that created the note (all doctor notes together all diagnosis notes all order notes etc.)

What I have come up with is a Serial number based system. Each dynamic table (whether a note table or a table to flag certain items..) has its own recordID (primary key autonum) as usual. It also has the RelatedRecordID of the row that is referencing it. To know which table the row references I use a TableID. This is a field that every table has. All the records of any particular table have the same value in this field. Thus it is easy to relate.

Now for the notes Ive thought of adding 2 TableID fields. One being the table that is actually creating the note. And the second being an OwnerTableID. Thus for instance the Patient(Customer) Table OWNS the Diagnosis table, the order table and so on. So I could easily do a relationship from the Patient table to the notes table and see all notes for that patient. Regardless of where they were created.

That's all for now. Just tellin what we are doing with Dataface.
Posts: 420
Joined: Wed Dec 31, 1969 5:00 pm

Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 1 guest

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved