Elevate DB 2.x, Delphi 2007, Windows Vista

This is a general question to get an idea about how others are handling this issue.

In a large application, there are likely to be many lookup items (e.g., gender, race, state etc. Then, there are things like employee names by profession
category - which could be one lookup table or many - this is the one I want you opinion about). In the end, we want to do the usual: In other tables in app
that reference employees, store lookup ID in "EmpID" field but show actual value (i.e., Name, in this case) from the look up table(s)

Option 1: To lookup employees by category, We can have a separate table for each category with fields TWO fields: EmpID : Integer; Employee Name :
varchar(). In this model, there is one table each for clerks, one for nurses, one for dieticians and so on. Needless to say, in a diverse environment like
hospital, this could end up creating scores of tables.

Option 2: For all employees, there is only one lookup table with extra field to specify profesion code: EmpID, ProfessionCode, Name.

#2 complicates DBLookupComboBox.ListSource issue (most likely it will require a query to get EmpID and Name by Profession code - unless someone
knows a better way).

As always, all help is appreciated.

JayM

Jay


I mainly use tables and follow a somewhat heretical approach. I'd probably do the same with SQL simply to allow sensitive cursors wherever possible. My app is recruitment and (amongst others) I have three tables:

Contacts
Career
JobCodes

I store name etc in Contacts, Career holds tenure, company etc and JobCodes the base job information.

Career has both the jobcode for each role and also a title (you would not believe the variation on job titles). When a new role is added for a contact when the job code is selected from the lookup table I pick up the standard description and slot that in. When a contact changes jobs the latest jobcode is slotted into the Contacts table (this can be overridden and allows flexibility).

The advantage is if I want to select all CEO's its a simple filter but I also have the option of selecting those who have been CEO's in prior roles.

My approach is that I'll split things into separate tables if there's a benefit but I have no problem with some properly managed data duplication if its advantageous.

Roy Lambert


CREATE TABLE "Contacts"
(
"_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_Title" VARCHAR(4) COLLATE "ANSI_CI",
"_Forename" VARCHAR(15) COLLATE "ANSI_CI",
"_Surname" VARCHAR(30) COLLATE "ANSI_CI",
"_Address1" VARCHAR(35) COLLATE "ANSI_CI",
"_Address2" VARCHAR(35) COLLATE "ANSI_CI",
"_Town" VARCHAR(35) COLLATE "ANSI_CI",
"_County" VARCHAR(35) COLLATE "ANSI_CI",
"_Country" VARCHAR(35) COLLATE "ANSI_CI",
"_PostCode" VARCHAR(10) COLLATE "ANSI_CI",
"_HomePhone" VARCHAR(25) COLLATE "ANSI_CI",
"_LastDPADate" DATE,
"_LastDPACheckBy" VARCHAR(30) COLLATE "ANSI_CI",
"_LastDPAAccessRequest" DATE,
"_LastDPASupplied" DATE,
"_Mobile" VARCHAR(25) COLLATE "ANSI_CI",
"_HomeEmail" VARCHAR(60) COLLATE "ANSI_CI",
"_LastRecruited" DATE,
"_LatestCVDate" DATE,
"_Placed" DATE,
"_fkJobCodes" VARCHAR(10) COLLATE "ANSI_CI",
"_fkMarkets" VARCHAR(10) COLLATE "ANSI_CI",
"_RecruitedBy" VARCHAR(3) COLLATE "ANSI_CI",
"_ContactInterval" VARCHAR(3) COLLATE "ANSI" DEFAULT '1m',
"_AttemptInterval" VARCHAR(3) COLLATE "ANSI" DEFAULT '1d',
"_ModDate" DATE,
"_Status" VARCHAR(10) COLLATE "ANSI_CI",
"_DoB" DATE,
"_YoB" SMALLINT,
"_LastCallBy" VARCHAR(3) COLLATE "ANSI_CI",
"_Source" VARCHAR(20) COLLATE "ANSI_CI",
"_fkStaff_LTC" VARCHAR(5) COLLATE "ANSI_CI",
"_LTCLastAttempt" DATE,
"_PNotes" VARCHAR(35) COLLATE "ANSI_CI",
"_NoMailShots" BOOLEAN DEFAULT FALSE ,
"_LatestCVType" VARCHAR(10) COLLATE "ANSI_CI",
"_LTCAlarm" TIMESTAMP,
"_LastCall" DATE,
"_Created" DATE DEFAULT CURRENT_DATE,
"_Partner" VARCHAR(60) COLLATE "ANSI_CI",
"_HiringManager" BOOLEAN DEFAULT FALSE ,
"_NoticePeriod" VARCHAR(10) COLLATE "ANSI_CI",
"_BaseSalary" VARCHAR(15) COLLATE "ANSI_CI",
"_SalaryDate" DATE,
"_SalaryWanted" VARCHAR(15) COLLATE "ANSI_CI",
"_WantedDate" DATE,
"_NoEshots" BOOLEAN DEFAULT FALSE ,
"_PackageInfo" CLOB COLLATE "ANSI_CI",
"_Links" CLOB COLLATE "ANSI_CI",
"_Notes" CLOB COLLATE "ANSI_CI",
"_Skills" CLOB COLLATE "ANSI_CI",
"_OtherEddresses" CLOB COLLATE "ANSI_CI",
"_DPAHistory" CLOB COLLATE "ANSI_CI",
"_Attributes" CLOB COLLATE "ANSI_CI",
"_UserFlags" CLOB COLLATE "ANSI_CI",
"_LatestCV" BLOB,
"_SellingPoints" CLOB COLLATE "ANSI_CI",
"_OtherPhoneNumbers" CLOB COLLATE "ANSI_CI",
"_Referrals" CLOB COLLATE "ANSI_CI",
"_References" CLOB COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_ID")
)


CREATE TABLE "Career"
(
"_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_fkContacts" INTEGER,
"_fkCompanies" INTEGER,
"_fkSites" INTEGER,
"_Started" SMALLINT,
"_Left" SMALLINT,
"_fkContacts_Manager" INTEGER,
"_UnlistedCompany" VARCHAR(60) COLLATE "ANSI_CI",
"_Secretary" VARCHAR(50) COLLATE "ANSI_CI",
"_SecEMail" VARCHAR(60) COLLATE "ANSI_CI",
"_SecPhone" VARCHAR(25) COLLATE "ANSI_CI",
"_MainPhone" VARCHAR(25) COLLATE "ANSI_CI",
"_Email" VARCHAR(60) COLLATE "ANSI_CI",
"_OkForEmail" BOOLEAN DEFAULT FALSE ,
"_fkJobCodes" VARCHAR(10) COLLATE "ANSI_CI",
"_JobTitle" VARCHAR(50) COLLATE "ANSI_CI",
"_Created" DATE DEFAULT CURRENT_DATE,
"_Autocclist" CLOB COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_ID")
)

CREATE TABLE "JobCodes"
(
"_JobCode" VARCHAR(10) COLLATE "ANSI_CI",
"_JobDesc" VARCHAR(35) COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_JobCode")
)

Jay

I would use #2 with following modification: one lookup table for employees
and one for profession code. The user can from latter select profession code
he/she wants and then he/she gets the appropriate list of employees from the
first table. This modification lets you append new modification codes nearly
without new coding if you provide the mechanism for updating the table with
profession codes.

Regards
Iztok Lajovic

"Jay M" <mavi.supravista@gmail.com> je napisal v sporoèilo
news:ACFD3A36-C73E-44E3-813F-B7517D3A6B29@news.elevatesoft.com ...
> Elevate DB 2.x, Delphi 2007, Windows Vista
>
> This is a general question to get an idea about how others are handling
> this issue.
>
> In a large application, there are likely to be many lookup items (e.g.,
> gender, race, state etc. Then, there are things like employee names by
> profession
> category - which could be one lookup table or many - this is the one I
> want you opinion about). In the end, we want to do the usual: In other
> tables in app
> that reference employees, store lookup ID in "EmpID" field but show actual
> value (i.e., Name, in this case) from the look up table(s)
>
> Option 1: To lookup employees by category, We can have a separate table
> for each category with fields TWO fields: EmpID : Integer; Employee Name :
> varchar(). In this model, there is one table each for clerks, one for
> nurses, one for dieticians and so on. Needless to say, in a diverse
> environment like
> hospital, this could end up creating scores of tables.
>
> Option 2: For all employees, there is only one lookup table with extra
> field to specify profesion code: EmpID, ProfessionCode, Name.
>
> #2 complicates DBLookupComboBox.ListSource issue (most likely it will
> require a query to get EmpID and Name by Profession code - unless someone
> knows a better way).
>
> As always, all help is appreciated.
>
> JayM
>

<< Roy Lambert wrote:

Jay

I mainly use tables and follow a somewhat heretical approach. I'd probably do the same with SQL simply to allow sensitive cursors wherever possible. My
app is recruitment and (amongst others) I have three tables:

Contacts
Career
JobCodes

I store name etc in Contacts, Career holds tenure, company etc and JobCodes the base job information.
-----------------------------<Snip>----------------------------------- >>

Roy:

Thanks for your input. To keep from having a gazillion lookup tables, I have decided to go with a few, each with same basic structure (e.g., ItemID, Value
and Comment fields). For example, General_Lookup table will have lookups for items that don't change with organization: Sex, Race, Salutation (Mr. Dr,
Mrs., Ms.), common Professional Degrees (in medical field), etc. Another lookup table, Org_Lookup, will have lookup data for organization's capmpuses,
Departments, Employee titles/jobs (things that change with each organization).

For each lookup item (there will be many in each lookup table), I will have a TQuery and a TDataSource on a DataModule - this will make it easy to access
fields for TDBLookupComboBox that I can place on any number of forms. This worked well in a small test app and does not overwhelm my little, shrinking
brain.

Thank You

JayM

<< "Iztok Lajovic" wrote:

Jay

I would use #2 with following modification: one lookup table for employees
and one for profession code. The user can from latter select profession code
he/she wants and then he/she gets the appropriate list of employees from the
first table. This modification lets you append new modification codes nearly
without new coding if you provide the mechanism for updating the table with
profession codes.

Regards
Iztok Lajovic ----------------------------------------------->>

Thank you Iztok. I was writing my reply to Roy's message while yours came in.

As I noted in reply to Roy and as YOU suggested, I have decided to go with option #2. Among other efficiencies, it certainly keeps number of files (tables
and indexes) manageable.

JayM

Jay,

<< Thank you Iztok. I was writing my reply to Roy's message while yours came
in.

As I noted in reply to Roy and as YOU suggested, I have decided to go with
option #2. Among other efficiencies, it certainly keeps number of files
(tables and indexes) manageable. >>

I would second Iztok's suggestion.  Just put the profession code directly in
the employees table, and then have one separate lookup table for the
profession codes.  You can then also setup of foreign-key constraints to
make sure that all employees reference a valid profession code.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thank you Roy, Iztok and Tim- I am going the way you suggest.

JayM