PDA

View Full Version : A table for lookup list or derive list on the fly


ABC123

Mike Wilson
10-20-2007, 10:45 AM
Good morning,

I have a contact manager application I am completing for a non-for-profit organization. It has at this point 39 tables, 16 of which will contain records with contact individuals information linked through a personal unique identifier (pqid) field. The client wants a method to delete (not archive or inactivate), contacts from the database. My intent is to open one-by-one the tables that contain pqid records filtered to the contact's pqid, and then <tbl>delete_range() the records. So as a starting point, I need a list of tables that contain pqid records. With the consideration that the delete event will be infrequent, my question is this:

Is it best practice to maintain another table that identifies the tables that contain pqid records as a lookup, OR generate this on the fly using A5.table_enum(), cycle through that list with table.external_field_name_get(),removing the tables without a pqid field to achieve the list of tables, OR some other way I haven't considered.

Thanks.

Peter.Greulich
10-20-2007, 01:16 PM
Is it best practice to maintain another table that identifies the tables that contain pqid records as a lookup, OR generate this on the fly using A5.table_enum(), cycle through that list with table.external_field_name_get(),removing the tables without a pqid field to achieve the list of tables, OR some other way I haven't considered.
.
Hhhmmm...

It seems to me that unless you will be adding/removing tables regularly or randomly, hard code the list. But, if the former, do it on the fly.

Tim Kiebert
10-20-2007, 08:41 PM
Hi Mike,

What Peter said.

Mike Wilson
10-20-2007, 09:48 PM
OK,
On the fly it will be. As dynamic as possible is my thinking. Thanks for the thoughts.

DaveM
10-23-2007, 06:05 PM
Mike,

May i ask why you would have pqid in 16 seperate tables? I know this not a part of your request, but why not single table? A field possibly to identify the purpose instead of another table???? Multiple tables makes for a lot of redundancy.

It would/could make life easier?

Maybe I just read the question wrong?

dave

Mike Wilson
10-24-2007, 01:51 PM
Dave,
you asked so I will tell you. It is a good exercise to recap the construct.

This is an expanded contact manager application for a non-for-profit organization centered on crisis intervention and counseling for church and evangelical pastors. The organization is called PastorCare (PC). So the tables and their purpose that hold pqid (personal unique id) and various info are:

contacts – Main demographics table for each contact.

referrals – This hold the pqid’s of contacts referrals to and from so the line of connection can be documented. Who referre whom to whom…. etc

relations - This holds 2 pqid’s per record that documents family, friend and business relationionship connections. Who knows whom, who is related to whom, etc

events - PC holds events such as seminars, fund raising banquets, family gatherings, etc. They sign people up, communicate with these groups, track attendance, and engage in follow up to attendees. This table holds those data.

comm_action_temp - When communicating with a group of people, such as a group scheduled to participate in an upcoming seminar, some want communication by mail, others email, others telephone calls. This is a temporary table that holds pqid's of each subgroup as it emails each, or prints mailing labels for each, or prints a list with telephone numbers for communication actions.

staff - PC is an organization of paid and volunteer personell. PC also provides counseling services. This table holds this data of whom these personell are, their status, position, activity levels, etc.

group_assign: PC contacts become part of groups, such as pastors, counselors, professionals (attorneys, doctors, CPA's), practical help (plumbers, mechanics, photograpers) etc. This is a central, major element of this database.

donations - This table holds the donation provided to PC

churches - A large table holding the names,addresses,pastors, etc of regional churches.

church – This is a table that holds the church a person belongs to what church as a component of the database that is being replaced. This will disappear after the complete build and transition into the this new A5 db.

s_error - This is a table that hold pqid and specific info about errors in the demographic of contacts when discovered and not yet resolved.

client_staff - This is part of a complex set that holds the person's registration into the client (counseling) services, the attachments of the primary and secondary (consulting) providers, and the counseling session events and details (date, time, duration, notes) etc. This section of the database has to have strick confidentiality safegaurds.

diary – This is the appointment scheduler for the main staff members.

interactions – Similar to an audit table, every interaction (meeting, telephone call, participation in an event, etc is logged in this table. The diary is the scheduling of interactions and is staff centered. The Interactions table is contact centered, and hold the pqid’s of all the people that have interacted with PC; the who, what, when where and how of the interactions. It is far beyond the Diary and provides for generating a list of all persons and their engagements with PC.

comm_way – This is a many child table to the Contacts table that holds the preferences of the contacts as to how they wish to be contacted for the various activities PC engages in. Some people want emails for general information/ announcements, but regular mail for special events like the annual banquet, and maybe receive nothing for the weekly newsletter. This is a source for dividing up the communication events that feeds the comm_action_temp table.

Alerts – This holds contact centered, non-sheduled, non-PC central mission based, futuristic information such as upcoming weddings, birthdays, anniversaries, special events in peoples lives, etc , which pastor people like to have registered to be available for acknowledgements.

Enough said.

martin horzempa
10-24-2007, 04:55 PM
hi Mike

you might look in the help files under
referential integrity


just a thought

Mike Wilson
10-24-2007, 06:14 PM
Martin,
Thank you. That applies to parent-child tables within a set. These are all mostly unlinked tables.

MoGrace
10-24-2007, 11:08 PM
Martin,
Thank you. That applies to parent-child tables within a set. These are all mostly unlinked tables.I've never tried referential integrity to delete records with that many tables linked. But you could create a set with the parent as contact and make the links either one to one or one to many as needed and use the set to mark the records. The form you do this on only needs to show limited info to identify the contact. Then buttons could mark or unmark specific records or globally. Then you could reiterate thru the tables to delete the marked records. If all the tables involved have a pqid field, you can link them on that field.

martin horzempa
10-24-2007, 11:47 PM
Hi Mike

i dont want to be disagreeable but i believe
that your particular problem is exactly what
relational database manegement is all about
and most of the leading dbms on the market
include some form of referential integrity and alpha uses
a "set" as the basis of maintaining this

think about it
you have a parent table "CONTACTS" that is linked by the
pqid field to many child tables

if you create a set based on this relationship you
are basically setting up a list of child records to be referenced by
the link to the parent field pqid- and one of the functions
that is ALREADY programmed into the set is the ability to do
cascading DELETES - which is what you are trying to achieve


so i believe that you can maintain your list and save yourself the
trouble of coding by using the "SET" to do what you want

if i am way off base here - i hope someone will set me straight


jmho

DaveM
10-25-2007, 06:55 PM
16 of which will contain records with contact individuals information linked through a personal unique identifier (pqid) field

Tgis is something I would work a month to avoid. Would rather have 1 table with a field to differentiate the person into a category. If they belong to more than one category, then a second category or a category field with letters in it that can be something like "A" for preacher and "B" for council member. a simple search of the field would yield all the preachers even if they are or not council members.

It is called redundancy reduction and worth it's weight in gold. The whole database becomes less complicated.

Yes Mike I think you need to use set(s) and reduce redundancy. A header table would be the alternative.

Then, Maybe i am wrong or misreading what i read??

DaveM

Mike Wilson
10-26-2007, 03:41 PM
Then, Maybe i am wrong or misreading what i read??

Dave, more like I wasn't very exact in what I was relating. One table holds the persons demographics and detailed information. The other 15 are like child tables and only hold the PQID, and quantifiers, qualifyers and notes as it pertains to that person's record within reasons and dealings for the particular table.

But, I didn't think about what Robin and Martin are relating... make one gargantuan set with all the tables linked to the parent Contacts, and engage referential integrity, and use that to fully delete a contact entry from the database. Yup, I get it now. I have just been so indoctrinated by Jim Chapman's article that teaches NOT to produce enormous sets.

Thanks

Tim Kiebert
10-26-2007, 11:32 PM
Mike,
If you need this set only occasionally and you won't be adding any layouts or operations to it then, since you like programming so much :), you could make the set 'on the fly' using the the <TBL>.RELATION_ADD() function. This way you won't have the set in the control panel and you won't have the corresponding group of files that go along with the set. Not that it takes up much disk space but just reduces clutter. And I know you would have fun doing it.:D

Mike Wilson
10-27-2007, 11:32 AM
Thanks Tim! Don't know that function, but will soon.

Speaking of liking to write scripts, did you see the monster Stan wrote in this thread?

http://msgboard.alphasoftware.com/alphaforum/showthread.php?t=69881