Help - Initial Design (1 Viewer)

[uusdw]

Hi all,

I am stuck as to where to start. My background is in programming and not entity modelling so am stuck as to how to start with a given scenario.
Any help would be appreciated:

Each client has a service package consisting of one or more elements of care. (These elements could include: Home Care, Night Care, Day Centre visits and Transport to and from a Day Centre).
All elements have a start date the service begins - with a new row needed every week the service remains open (per client).

Each client has to be assessed to establish his or her ability to contribute towards the cost of this care.

A client has a visit where financial information is collected in order to carry out a financial assessment of that clients ability to pay for one or more services.
One client can have many visits (as re-assessments take place on an annual basis)

The Assessment calculates a disposable income (which is the maximum amount a client can be asked to contribute towards the cost of all their services combined)

So whilst a client can have many elements of care, they can only be asked to contribute one amount (their disposable income level) for all their ‘Active’ elements combined

A new assessment can also take place without visiting the client as it is possible for a client to do a self-assessment.


The tables I have so far are:

tblClient
tblServicePackage (I’m thinking this will contain all active services pulled from their individual tables??)

(types of service)
tblHomeCare
tblNightCare
tblDayCentre
tblTransport

tblVisit

tblFinancialAsssessment

I really am stuck and just want something relational as I am fine with VBA, Forms, Queries, Macros etc..

Any help appreciated!

 

[MarkK]

Here're a few quick thoughts...

tClient
 - defines the client info, might include financials?
tServiceType 
 - defines the services you offer
 - Home Care, Day Centre, etc... as data.
tService
 - defines the relationship between the services you offer
   and the clients who receive them
 - Fields include, ID, ClientID, ServiceTypeID, StartDate, EndDate
 - New record every week?  Why?  What data is added weekly?
tFinance
 - appears to have a one-to-one relationship with client
 - might not be needed