Family

Introduction

This section introduces the structure of the ‘family’ table and its relationships within the database. It specifically focuses on the connections between family members, such as the relationship between parents and children. This documentation aims to provide an overview of how family entities are interconnected.

FAM_Family

Field	Type	Index	Description
FamilyID	int identity	PK	Primary key, unique identifier
DisplayAsName	nvarchar(60)		Display as name
CaseNo	nvarchar(30)		Case number
CountyNumber	nvarchar(80)		County number
CountyScreen	nvarchar(32)		County screen
Stage1StarDate	datetime		Stage 1 start date
Stage1EndDate	datetime		Stage 1 end date
Stage2StarDate	datetime		Stage 2 start date
Stage2EndDate	datetime		Stage 2 end date
Stage3StarDate	datetime		Stage 3 start date
Stage3EndDate	datetime		Stage 3 end date
LastName	nvarchar(30)		Last name
FirstName	nvarchar(30)		First name
MiddleInitial	nvarchar(10)		Middle initial
FamilyType	nvarchar(32)		Family type (Natural, Comparing)
ParentSize	nvarchar(32)		Parent size (Single Parent, Parent Family)
FamilySize	int		Family size
FamilyMonthIncome	decimal(18, 2)		Family monthly income
FamilyFeeHourly	decimal(18, 2)		Family fee per hour
FamilyFeeFT	decimal(18, 2)		Family fee full time
FamilyFeePT	decimal(18, 2)		Family fee part time
PublicSSNumber	bit		Is SSN public (Y/N)
Zip	nvarchar(10)		Zip code
Address	nvarchar(300)		Address
Unit	nvarchar(300)		Unit number
City	nvarchar(30)		City name
County	nvarchar(50)		County name
State	nvarchar(30)		State name
CityID	int		City ID
CountyID	int		County ID
StateID	int		State ID
SameAddress	bit		Same address flag (Y/N)
MailZip	nvarchar(10)		Mailing zip code
MailAddress	nvarchar(300)		Mailing address
MailUnit	nvarchar(300)		Mailing unit number
MailCity	nvarchar(30)		Mailing city name
MailCounty	nvarchar(50)		Mailing county name
MailState	nvarchar(30)		Mailing state name
MailCityID	int		Mailing city ID
MailCountyID	int		Mailing county ID
MailStateID	int		Mailing state ID
EMailAddress	nvarchar(80)		Email address
HomePhone	nvarchar(35)		Home phone number
WorkPhone	nvarchar(35)		Work phone number
WorkPhoeType	nvarchar(32)		Work phone type (Ext, Direct)
Cell	nvarchar(35)		Cell phone number
RcvMsg	bit		Receive messages (Y/N)
Languange	int		Language code (1: English, 2: Spanish)
Contract	int		Contract ID (Stage 2; APP)
CashAid	nvarchar(32)		Cash aid status (Never, Currently Receiving, Termed from Cash Aid)
TermDate	datetime		Termination date
CalWorkActivity	nvarchar(100)		CalWork activity
Prohibit	bit		Prohibit (0 No, 1 Yes)
ProhibitReson	nvarchar(32)		Prohibit reason
BestCallNum	nvarchar(100)		Best call number
BestCallTime	nvarchar(100)		Best call time
CompanionType	int		Companion type (0: Normal, 1: Companion, 2: HasCompanion)
CompanionFamilyID	int		Companion family ID
Status	int		Status
Comment	nvarchar(max)		Comment
CaseManager	int		Case manager
OwnerId	int		Owner ID
StartDate	datetime		Start date
EndDate	datetime		End date
History	int		History version
Disable	int		Disabled status (0 No, 1 Yes)
CreateUserId	int		ID of the user who created
UpateUserId	int		ID of the user who updated
Version	int		Version
CPS	nvarchar(32)		CPS
FeeWaiver	bit		Fee waiver (Y/N)
FeeWaiverStartDate	datetime		Fee waiver start date
FeeWaiverEndDate	datetime		Fee waiver end date
CAPPStartDate	datetime		CAPP start date
CAPPEndDate	datetime		CAPP end date
Homeless	int		Homeless
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

Fam_FamilyExtension

Extended family information, mainly for future custom extensions.

FAM_Child

Field	Type	Index	Description
ChildID	int identity	PK	Primary key, unique identifier
FamilyID	int	CL, IX	Foreign key to family
LastName	nvarchar(100)		Last name
FirstName	nvarchar(100)		First name
MiddleInitial	nvarchar(10)		Middle initial
DateOfBirth	datetime		Date of birth
Gender	nvarchar(32)		Gender
Grade	decimal(18, 2)		Grade
SSNumber	nvarchar(30)		Social Security Number
PublicSSNumber	bit		Is SSN public (Y/N)
Language	int		Language ID
LimitedEnglish	bit		Limited English proficiency (Y/N)
Ethnicity	int		Ethnicity ID
Race	int		Race ID
CitizenInfo	nvarchar(20)		Citizen info ID
Zip	nvarchar(10)		Zip code
Address	nvarchar(300)		Address
Unit	nvarchar(300)		Unit number
City	nvarchar(30)		City name
CityID	int		City ID
County	nvarchar(30)		County name
CountyID	int		County ID
State	nvarchar(30)		State name
StateID	int		State ID
Relationship	int		Relationship ID
IncludeInSize	bit		Include in size (Y/N)
AdjustFactor	decimal(18, 2)		Adjust factor
MinorParent	bit		Minor parent (Y/N)
IEPLastMeeting	datetime		Last IEP meeting date
IEPNextMeeting	datetime		Next IEP meeting date
Status	int		Status
Comments	nvarchar(max)		Comments
StartDate	datetime		Start date
EndDate	datetime		End date
History	int		History version
Disable	bit	IX	Disabled status (0 No, 1 Yes)
CreateUserId	int		ID of the user who created
UpateUserId	int		ID of the user who updated
Version	int		Version
IsProtectiveService	bit		Is protective service (Y/N)
CID	nvarchar(100)		Child identifier
MultipleRace	varchar(100)		Multiple race
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

Fam_ChildExtension

Extended child information, mainly for future custom extensions.

FAM_Parent

Field	Type	Index	Description
ParentID	int identity	PK	Primary key, unique identifier
FamilyID	int	IX, CL	Foreign key to family
LastName	nvarchar(30)		Last name of the parent
FirstName	nvarchar(30)		First name of the parent
MiddleInitial	nvarchar(10)		Middle initial of the parent
DateOfBirth	datetime		Date of birth
Gender	nvarchar(32)		Gender of the parent
SSNumber	nvarchar(30)		Social Security Number
PublicSSNumber	bit		Is SSN public (Y/N)
Language	int		Language ID
LimitedEnglish	bit		Limited English proficiency (Y/N)
Ethnicity	int		Ethnicity ID
Race	int		Race ID
CitizenInfo	int		Citizen Information ID
Zip	nvarchar(10)		Zip code
Address	nvarchar(300)		Home address
Unit	nvarchar(300)		Unit number
City	nvarchar(30)		City name
CityID	int		City ID
County	nvarchar(30)		County name
CountyID	int		County ID
State	nvarchar(30)		State name
StateID	int		State ID
HomePhone	nvarchar(35)		Home phone number
WorkPhone	nvarchar(35)		Work phone number
WorkPhoneType	nvarchar(32)		Work phone type
Cell	nvarchar(35)		Cell phone number
RcvMsg	bit		Receive messages (Y/N)
Email	nvarchar(50)		Email address
Relationship	int		Relationship ID
MaritalStatus	nvarchar(32)		Marital status
Responsibility	nvarchar(32)		Responsibility description
IncludeInSize	bit		Include in family size (Y/N)
MonthlyIncome	decimal(18, 2)		Monthly income
TrainingStartDate	datetime		Training start date
TrainingEndDate	datetime		Training end date
Status	int		Status
Comments	nvarchar(max)		Comments
StartDate	datetime		Start date
EndDate	datetime		End date
History	int		History version
Disable	int		Disabled status
CreateUserID	int		ID of the user who created
UpdateUserID	int		ID of the user who updated
Version	int		Version
BestCallNum	nvarchar(100)		Best call number
BestCallTime	nvarchar(100)		Best call time
PID	nvarchar(100)		Parent ID
CommunicationMethod	nvarchar(100)		Preferred communication method
MultipleRace	varchar(100)		Multiple races description
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

Fam_ParentExtension

Extended parent information, mainly for future custom extensions.

FAM_FamilyFee

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
FamilyId	int	IX	Foreign key to family
StartDate	datetime		Start date of the fee period
EndDate	datetime		End date of the fee period
IsEffective	bit	IX	Whether the fee is effective (Y/N)
FamilySize	int		Size of the family
MonthlyIncome	decimal(18, 3)		Monthly income of the family
HourlyFee	decimal(18, 3)		Hourly fee
FullTimeDailyFee	decimal(18, 3)		Full-time daily fee
PartTimeDailyFee	decimal(18, 3)		Part-time daily fee
Status	int		Status
Comment	nvarchar(max)		Comments
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
CaseId	int	IX, _dta	Case ID
IncomeSource	nvarchar(max)		Source of income
SendMode	int default 0		Mode of sending
UserDefined	bit		User-defined flag
SMIMode	int		SMI mode
CeilingMonthlyIncome	decimal(38, 6)		Ceiling monthly income, default 0
Eligible	bit default 0		Eligibility flag
CalculateExpression	nvarchar(2048)		Expression for calculation
ContractId	int default 0		Contract ID
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

FAM_MemberIncome

Field	Type	Index	Description
IncomeID	int identity	PK	Primary key, unique identifier
FamilyID	int		Foreign key to family
ParentID	int	CL, IX	Foreign key to parent
ChildID	int		Foreign key to child
IncomeType	nvarchar(32)		Type of income
IncomeSource	nvarchar(32)		Source of income
PayType	nvarchar(32)		Type of pay
PayScheduleType	nvarchar(32)		Pay schedule type
RateType	nvarchar(32)		Rate type
Rate	decimal(18, 2)		Rate of pay
PayRateNumber	decimal(18, 2)		Number of pay rates
Fluctuate	bit		Fluctuate (Y/N)
YTD	bit		Year to date (Y/N)
YTDNum	decimal(18, 2)		Year to date number
YTDStartDate	datetime		Year to date start date
YTDEndDate	datetime		Year to date end date
MonthlyIncome	decimal(18, 2)		Monthly income
TenMonthFormula	bit		Ten month formula (Y/N)
Total	decimal(18, 2)		Total income
BizExp	decimal(18, 2)		Business expenses
Month	decimal(18, 2)		Month
StartDate	datetime		Start date
EndDate	datetime		End date
Status	int		Status
Comment	nvarchar(max)		Comments
History	int		History version
Disable	int		Disabled status
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
EDocID	nvarchar(50)		Electronic document ID
IsEffective	bit		Effective status (Y/N)
NeedID	int		Need ID
CalculateExpression	nvarchar(300)		Expression for calculation
Amount	decimal(18, 2)		Amount
Frequency	nvarchar(32)		Frequency
IsWages	bit		Is wages (Y/N)
Days	int		Number of days
Hours	int		Number of hours
ManualAdjust	decimal(18, 2)		Manual adjustment
PayTypeDetailId	int		Pay type detail ID
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

FAM_MemberIncomeDetail

Field	Type	Index	Description
IncomeDetailID	int identity	PK	Primary key, unique identifier
IncomeID	int	CL, IX	Foreign key to income
Name	nvarchar(100)		Name
Income	decimal(18, 2)		Income amount
PayDate	nvarchar(100)		Pay date
Comment	nvarchar(max)		Comments
Disable	int		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
Hours	decimal(18, 4)		Number of hours
PayeeNo	nvarchar(100)		Payee number
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

FAM_MemberNeeds

Field	Type	Index	Description
NeedID	int identity	PK	Primary key, unique identifier
FamilyID	int		Foreign key to family
ParentID	int	CL, IX	Foreign key to parent
IncomeID	int		Foreign key to income
Name	nvarchar(100)		Employer/School Name
Type	int		Need type
IsVariable	bit		Is variable (Y/N)
IsOnlineClass	bit		Is online class (Y/N)
Address	nvarchar(300)		Address
Unit	nvarchar(300)		Unit number
Zip	nvarchar(30)		Zip code
City	nvarchar(30)		City name
CityID	int		City ID
County	nvarchar(30)		County name
CountyID	int		County ID
State	nvarchar(30)		State name
StateID	int		State ID
CompanyAddress	nvarchar(200)		Company address
CompanyUnit	nvarchar(300)		Company unit number
CompanyZip	nvarchar(30)		Company zip code
CompanyCity	nvarchar(30)		Company city name
CompanyCityID	int		Company city ID
CompanyCounty	nvarchar(30)		Company county name
CompanyCountyID	int		Company county ID
ContactTitle	nvarchar(100)		Contact title
ContactName	nvarchar(100)		Contact name
ContactPhone	nvarchar(35)		Contact phone number
ContactPhoeType	nvarchar(32)		Contact phone type
ContactEmail	nvarchar(50)		Contact email
ContactFax	nvarchar(35)		Contact fax
TravelTimeBefore	decimal(18, 2)		Travel time before
TraverTimeAfter	decimal(18, 2)		Travel time after
NonPaidLunchTime	decimal(18, 2)		Non-paid lunch time
UseDays	decimal(18, 2)		Number of use days
Units	decimal(18, 2)		Units for training need
CompleteVariable	bit		Complete variable (Y/N)
Status	int		Status
EDocID	nvarchar(50)		Electronic document ID
Comment	nvarchar(max)		Comments
StartDate	datetime		Start date
EndDate	datetime		End date
TrainingGrade	int		Training grade for training need
History	int		History version
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
SameAddress	bit		Same address flag (Y/N)
IsEffective	bit		Effective status (Y/N)
Patient	nvarchar(32)		Patient
Title	nvarchar(200)		Title
CalWorkActivity	nvarchar(100)		Work activity calculation
DateOfHire	datetime		Date of hire
Meta	nvarchar(max)		Metadata
Schedule	nvarchar(max)		Schedule
MaxHoursPerWeek	decimal(38, 6)		Maximum hours per week
MaxDaysPerWeek	decimal(38, 6)		Maximum days per week
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

FAM_MemberSchedule

Field	Type	Index	Description
ScheduleID	int identity	PK	Primary key, unique identifier
NeedID	int	CL, IX	Foreign key to need
NeedScheduleType	nvarchar(32)		Schedule type (0:NormalTime, 1:SleepTime/Study time, 2:Summary)
MaxHoursPerWeek	decimal(18, 2)		Maximum hours per week
MaxDaysPerWeek	decimal(18, 2)		Maximum days per week
MaxHoursPerDay	decimal(18, 2)		Maximum hours per day
MonIn	datetime		Monday in time
MonOut	datetime		Monday out time
TueIn	datetime		Tuesday in time
TueOut	datetime		Tuesday out time
WedIn	datetime		Wednesday in time
WedOut	datetime		Wednesday out time
ThuIn	datetime		Thursday in time
ThuOut	datetime		Thursday out time
FriIn	datetime		Friday in time
FriOut	datetime		Friday out time
SatIn	datetime		Saturday in time
SatOut	datetime		Saturday out time
SunIn	datetime		Sunday in time
SunOut	datetime		Sunday out time
Comment	nvarchar(max)		Comments
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserID	int		ID of the user who created
UpateUserID	int		ID of the user who updated
Version	int		Version
IsManual	bit		Is manual (Y/N)
IsVariable	bit default 0		Is variable (Y/N)
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

FAM_NeedSummary

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
FamilyId	int		Foreign key to family
CaseId	int		Case ID
Description	nvarchar(2048)		Description
Meta	nvarchar(max)		Metadata
IsVariable	bit		Is variable (Y/N)
StartDate	datetime		Start date
EndDate	datetime		End date
Disable	bit		Disabled status (Y/N)
CreateUserId	bigint		ID of the user who created
UpdateUserId	bigint		ID of the user who updated
Version	bigint		Version
Schedule	nvarchar(max)		Schedule
MaxHoursPerWeek	decimal(38, 6)		Maximum hours per week
MaxDaysPerWeek	decimal(38, 6)		Maximum days per week
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

FAM_NeedSummarySchedule

Field	Type	Index	Description
ScheduleID	int identity	PK	Primary key, unique identifier
NeedSummaryId	int		Foreign key to need summary
NeedScheduleType	nvarchar(2048)		Schedule type
MaxHoursPerWeek	decimal(38, 6)		Maximum hours per week
MaxDaysPerWeek	decimal(38, 6)		Maximum days per week
MaxHoursPerDay	decimal(38, 6)		Maximum hours per day
MonIn	datetime		Monday in time
MonOut	datetime		Monday out time
TueIn	datetime		Tuesday in time
TueOut	datetime		Tuesday out time
WedIn	datetime		Wednesday in time
WedOut	datetime		Wednesday out time
ThuIn	datetime		Thursday in time
ThuOut	datetime		Thursday out time
FriIn	datetime		Friday in time
FriOut	datetime		Friday out time
SatIn	datetime		Saturday in time
SatOut	datetime		Saturday out time
SunIn	datetime		Sunday in time
SunOut	datetime		Sunday out time
Comment	nvarchar(2048)		Comments
IsManual	bit		Is manual (Y/N)
Disable	bit		Disabled status (Y/N)
CreateUserId	bigint		ID of the user who created
UpateUserId	bigint		ID of the user who updated
Version	bigint		Version
IsVariable	bit default 0		Is variable (Y/N)
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

FAM_NOA

Field	Type	Index	Description
NOAID	int identity	PK	Primary key, unique identifier
FamilyID	int		Foreign key to family
DocumentID	int		Document ID
Action	nvarchar(32)		Action taken (1: Services Approved, 2: Services Denied, 3: Change in Service, 4: Termination of Service, 5: Termination of Service for Delinquent Fees)
ReasonCode	nvarchar(300)		Reason code
Reason	nvarchar(1024)		Reason
Distribution	int		Distribution method (0: GivenToParent, 1: MailedFirstClass, 2: MailedOther)
MailOtherTxt	nvarchar(max)		Other mail text
Eligiblity	int		NOA Eligibility
NeedType	int		NOA NeedType
IssueDate	datetime		Issue date
EffectiveDate	datetime		Effective date
AppealDate	datetime		Appeal date
Status	int		Status (0: Issued, 1: OnAppeal, 2: Rescinded, 3: Void)
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserId	int		ID of the user who created
UpateUserId	int		ID of the user who updated
Version	int		Version
CaseId	int	IX	Case ID
CareID	nvarchar(1024)		Care ID
CertOnly	bit		Certification only flag
BatchCode	nvarchar(2048)		Batch code
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time

FAM_EnhancedReferral

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
CaseId	int	IX	Foreign key to case
FamilyID	int		Foreign key to family
ChildID	int		Foreign key to child
ReferralDate	datetime		Referral date
Comment	nvarchar(500)		Comment
Status	int		Status
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserId	int		ID of the user who created
UpateUserId	int		ID of the user who updated
Version	int		Version
RequestDate	datetime		Request date
GearsUpdated	bit		Gears updated (0 No, 1 Yes)
Source	nvarchar(100)		Source
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time
Meta	nvarchar(max)		Metadata

FAM_EnhancedReferralProvider

Field	Type	Index	Description
Id	int identity	PK	Primary key, unique identifier
ReferralId	int	IX	Foreign key to referral
ProviderName	nvarchar(100)		Provider name
Contact	nvarchar(100)		Contact name
Phone	nvarchar(30)		Phone number
VacancyDate	datetime		Vacancy date
Status	int		Status
Disable	bit		Disabled status (0 No, 1 Yes)
CreateUserId	int		ID of the user who created
UpateUserId	int		ID of the user who updated
Version	int		Version
CreateTime	datetimeoffset		Record creation time
UpdateTime	datetimeoffset		Record update time