[tirrigh-heralds] Client Tracking database

[Quentin Martel]

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

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://tirrigh.org/pipermail/collegeofheralds_tirrigh.org/attachments/20070606/0be38303/attachment.htm>