[tirrigh-heralds] Client Tracking database

[Quentin Martel]

Great point! Something I should clarify for everyone:

The client table is information about each person, whether it's someone who
is submitting a piece of heraldry or the consulting herald. It's probably a
misnomer to call the table client, since it's going to be used two different
ways.

- QSY

-----Original Message-----
From: Judy Harcus [mailto:jharcus at prcn.org] 
Sent: Thursday, June 07, 2007 6:27 AM
To: Quentin Martel
Subject: Re: [tirrigh-heralds] Client Tracking database

The current form also has contact info for the consulting herald.  I was
contacted by Lions Blood once regarding a question on a client's submission
that enabled them to continue processing the submissions rather than return
it for clarification.  I don't know how often this happens but it can't if
you don't have the contact info.

Alicia
False Isle Pursuivant

Quentin Martel wrote:


	Greetings unto the Tir Righ College of Heralds!

	 

	 

	This is a quick update on one of our projects, the client tracking
database. 

	 

	First, I decided that the goal of this project is only to track
where our clients are in the submissions process. It is not designed to
track all of the heraldic details of that process. That may be added later,
but we need to take small steps with this. Besides, that information is more
appropriate for OSCAR and we're not trying to duplicate those efforts.

	 

	Accordingly, we have two tables, one to track information about the
person himself and one to track the information about the submission itself.


	 

	Those two tables are Client and Submission. Client has the following
fields:

	            [ClientID] [int] IDENTITY (1, 1) NOT NULL , - The unique
identifier of the row in the table

	            [SCAName] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL , - the SCAName of the person. This is
required since we identify people by name and branch

	            [Branch] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL , - the Branch of the person. This is
required since we identify people by name and branch

	            [ModernName] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , - the modern name of the person.

	            [Address] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , - The street address of the person

	            [Phone] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , - the phone number of the person

	            [Email] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , - the email address of the person

	            [Birthdate] [datetime] NULL , - the birthdate of the
person

	            [LastUpdate] [datetime] NOT NULL CONSTRAINT
[DF_Client_LastUpdate] DEFAULT (6 / 6 / 2007), - the date this row was last
updated. Useful for internal reasons

	 

	Submission has the following fields

	 

	            [SubmissionID] [int] IDENTITY (1, 1) NOT NULL , - Unique
identifier of the submission

	            [ClientID] [int] NOT NULL , - the person who is
submitting heraldry. Required

	            [HeraldID] [int] NULL , - the consulting herald

	            [Type] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL , - the type of submission (Name,
Device, Badge)

	            [Notes] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL , - a notes field for information about
the submission

	            [StartDate] [datetime] NOT NULL , - the date the process
started. This is a sketchy date, but basically it's when the client got
serious about wanting to register his heraldry. Required

	            [KSubDate] [datetime] NULL , - the date the heraldry was
submitted to Kingdom

	            [ILOIPubDate] [datetime] NULL , - the date the heraldry
was published in the ILOI

	            [KRetDate] [datetime] NULL , - the date the heraldry was
returned by Kingdom for more work

	            [KPassDate] [datetime] NULL , - the date the heraldry
passed Kingdom

	            [LOARPubDate] [datetime] NULL , - the date the heraldry
was published in the LOAR

	            [LRetDate] [datetime] NULL , - the date the heraldry was
returned by Laurel

	            [LPassDate] [datetime] NULL , - the date the heraldry
was passed by Laurel (in other words, it was registered)

	            [LastUpdate] [datetime] NOT NULL CONSTRAINT
[DF_Submission_LastUpdate] DEFAULT (6 / 6 / 2007), - the date this row was
last updated

	 

	From a technical side, the computer can look at the dates of Start,
KSub, ILOI, et al and see what the latest date is. That's the stage the
heraldry is in. From that information, we can make lists of who has what
where and keep on top of them.

	 

	So does this look good? Are we missing anything? Are there any
superfluous fields?

	 

	- Quentin Silver Yale

	 

	 

	
________________________________


	_______________________________________________
	Collegeofheralds mailing list
	Collegeofheralds at tirrigh.org
	
http://mail.tirrigh.org/mailman/listinfo/collegeofheralds_tirrigh.org