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.
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.