Table Design (1 Viewer)

rkm

Registered User.
Local time
Today, 06:11
Joined
May 14, 2015
Messages
12
I’m working on a project for a homeless shelter. They need to keep track of a lot of information and generate a number of reports for the various interested parties.

When a client requests aid in finding a permanent residence a “case” is created, whereby, the necessary information is captured (demographic, financial, legal, etc.) A client could have multiple “cases”.

I'm having a bit of a dilemma in trying to design the tables. My first instinct was to create tables for each category of information (demographic, financial, legal, health, etc.) since this would make form design and table maintenance easier, however, I would have to create one-to-one relationships for each of the tables and there would be 10 or 11 tables. Not to mention the one-to-many relationship between client and “case”.

I then thought I should probably design it as 2 tables; one for client information and the other for each “case”. The issue with this is that the “case” table would have over 100 fields (columns) in it. I’ve been trying to find information as to how many is too many but haven’t had much success. I know that if a table gets too wide (too many columns) it can affect the performance of the program.

The next idea would be a sort of compromise and to split the “case” table into 2 or 3 tables with a one-to-one relationship, but I’m thinking this might make maintenance and querying a bit of a nightmare.

So I’m looking for some advice as to how to handle this.
Question 1: In your experience, how many fields are too many?
Question 2: How would you handle this?
Thanks, in advance, for your help.
 

JHB

Have been here a while
Local time
Today, 11:11
Joined
Jun 17, 2012
Messages
7,732
..
I then thought I should probably design it as 2 tables; one for client information and the other for each “case”. The issue with this is that the “case” table would have over 100 fields (columns) in it.
Why should it have 100 fields, (we need to know the way you're thinking :))?
What could “cases” be, and do some or all “cases” require the same registration?
 

rkm

Registered User.
Local time
Today, 06:11
Joined
May 14, 2015
Messages
12
Thanks for replying,

It has over 100 fields because that is the requirement by the shelter and the interested parties. They need to capture, retain and report on all of this data.

The type of information they wish to collect are things like: demographic information (gender, ethnicity, military info, etc.), financial information (bank accounts, taxes, trustee info, income sources, etc.), health information (diagnosed physical issues, mental issues, addictions, prescriptions, etc.)

Each case is defined as whenever a client requests aid in finding permanent residency. So, if a homeless client wants help in finding a place to live, a case is created. If that client becomes homeless again and wants help to find another place, another case is created.

Each case must contain all of the above, required information. So, yes, some of the information will be repeated but that is the requirement, as they need to retain a record of each individual case and the information as it was at the time of processing.

I hope this answers your question. If not, I will try to clarify further.

Thanks.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:11
Joined
Sep 21, 2011
Messages
14,050
Some of your data is going to change with each case for the same client.
OK, their sex, military history etc would remain the same, but income, bank a/cs etc *could* be different, so I would be looking to have these linked to the case and client. Would you have two clients for one case?

So I would have thought, some data remains in the client table and the changing info in another table linked to client and or case.?

I'd be interested in the expert opinions on this.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:11
Joined
Jan 23, 2006
Messages
15,364
There is some online material here regarding various types of info captured/reported. There is also info from McGill here. These might be useful reference/background material.

I could not find an existing data model re homeless ... but did find this link to generic models that you may find useful as a framework.

It is rare to see a table nearing 100 fields. Better to create a clear, detailed description of WHAT your business is and then review it as per this tutorial to get entities, attributes and relationships in your business context. I would never start out sayng, I think I'll have 2 tables... let the business description "tell" you what "things" are involved.
Test your model with test data and scenarios to ensure you can store and retrieve/manipulate the data you need to support the business. Refine and adjust as necessary. Then build the data base with your data model as blueprint.

Good luck.
 
Last edited:

rkm

Registered User.
Local time
Today, 06:11
Joined
May 14, 2015
Messages
12
Thanks, jdraw.

I will review the literature and see what I can come up with.
 

rkm

Registered User.
Local time
Today, 06:11
Joined
May 14, 2015
Messages
12
Some of your data is going to change with each case for the same client.
OK, their sex, military history etc would remain the same, but income, bank a/cs etc *could* be different, so I would be looking to have these linked to the case and client.

That's what I thought at first, however, after thinking about it I think I only need to have the information that doesn't change to be in the client table and all else to be in case table. Then create a 1:M relationship from client to case.

Would you have two clients for one case?

No, but there could be multiple cases per client.
 

Users who are viewing this thread

Top Bottom