newbie que:how big should my data field tables be?

[nancyc]

ok this is my first attempt at a real database, and all my books are about five and six so its sort of frustrating to try to figure out 7...any help would be appeciated. here's the prob: we have clients, they serve families of subclients themselves, we need to track each kid in each family for times being served, times vary depending on day of week and days may vary by kid even within families, eligibilty starts and may end at differetn dates for each kid, we need to track kids times and days of eligibility and start and end dates, and link it to the original clients info to produce a monthly report updating each kid's status, dynamic things like the kid aging out of the program will affect eligibilty as well as stuff like the family moving away. this is just one small part of the database. we need to also track all kinds of info about the primary clients who serve the kids, like thier eligibility to be in the program too, and their rates of reimbursement for services they provide to the kids, which can vary by kid and by month based on factors like where they live (which can also change) and income levels of the kids being served. my question is, how big should i make my tables, i am using paper forms we currently have to identify and create appropriate fields of data we want to use and am getting info entered on an excel spreadsheet, preparatory to uploading it into the filemaker database. as we enter the data i realize i have to add more fields as information varies even more than i thought it would as far as answers to the questions we ask people. so at this point i have like four datafiles i've desigend, one with 275 fields, one with about 100, on with about 50 and one with 2. I'm sort of proud of the 2 field one i think it represents my understanding how a file can relate better, but my real question is, how do i know how to break the data tables down to manageable bite sizes. is 275 fields way too many or does it simply depend on the situation? should i just make lots of tiny table and relate the hekc out of them, or try to structure my tables to match the forms we use and related data we gather?

FileMaker Version: 7

Platform: Mac OS X Jaguar

[nancyc]

maybe this question was too general, i seem to have had more luck with getting answers to more specific questions, let me retry here...if i have a file of client information and also information on subclients (families) served by that client, for example client #1 (identified by a unique identifier number) has 16 families that they serve in this year so far, and i want to relate each child in each family to the client to see how many are at that client at particular times and days, first off, would it be more approrpiate to use a separate data table of information for each child, as opposed to trying to get all the children at that client into one data table? if we want to produce a report that shows all the children at that client and the scheduled times adn days each child is there currently (by month). the enrollment forms we use are by family, not by child, so as i understand it we need to now give a specific unique identifier number to 1) each enrollment form, since that is the data entry raw document, and 2) to each child entered, as there could be more than one enrolled from each family, and since additional children could be enrolled later from the same family after sibs were previously enrolled. then as i understand it i need to set up a small data set of fields (whcih i am calling a table) for each child, with the identifier number for each child to make that file unique and the identifier number for each client the child is at to allow me to relate it to all the children with that client. do i also need to give an identifier number the the FAMILY the child is from, i suppose i need to do that also, if i want to track families in any way...sorry, i'm really trying to think through the design stage here to make sure that i can structure the data sets so i can get meaningful relationships. ..as i understand it, the difference between this and excel is that in excel the data all has to be in the same table to be manipulated effectively, which is why i am tending towards larger data field sets, as i have used excel extensively, whereas here in filemaker, the data needs to be in different data tables to be manipulated against other tables to get (find) the relationship reports. is that a difference in approach i'm still mulling over, but i think i'm on the right track here...so...instead of having one file with all the parent and child info in it for each provider, i'd have a file for each provider, a f ile of fields for each family just identifying the family by unique number and putting in date entering program, location, parents info and contact info, then i'd have a separate set of data files for each child, relating the child by unique id number of its own to each familys id number so i can find all teh kids in a family....independent of when they enroll...so instead of a huge data field called child registration, i'd have a set of data called parent registration, antoher set called child registration, and tehy wold both relate back to teh client who serves them...conceptually, does this make sense? am i thinking in the right way here?

[Ender]

Take a breath there nancyc.

I'm not sure what your Client table is for, but the other parts of your project sound familiar to me. I have an enrollment solution that has the Familiy-Child-Enrollment tables set up exactly as you described (See ER diagram.)

For additional tables, think about each entity carefully, and how it may relate to each of the other entities. This is not a trivial process, so take your time and ask co-workers questions about how the process works (or should work.)

Gotta go...

FileMaker Version: Dev 6

Platform: Mac OS X Panther

[nancyc]

thank you, that helps, so the smaller the related files, the more control you have in defining the relationships between them, and getting data in them, is what i see

FileMaker Version: 7

Platform: Mac OS X Jaguar

[LaRetta]

Nancy said ... the more control you have in defining the relationships between them, and getting data in them, is what i see.

Size isn't the determining factor here but rather that 1:n (one-to-many) should be a separate table/file (usually). And you are correct in that defining relationships is easier with these 'sub-sets.' But also, the more segregated the

[nancyc]

thanks again, and yes, i've read the Key Concepts paper, but only once so far. I'll go back and re-read it. I know settting up the data flow relationships is the most crucial part, and you're sort of stuck once you set them. one advantage/disadvantage, whatever is that i have really only started working in filemaker, unfortunately, there are no books out there, and so i'm stuck reading the version six stuff for guidance and pestering people here :>

so...what i'm thinking here now is...based on the structure you suggested, how would i accomodate

1) changes in family address if the family moved, or changes in the custodial parents if the family moved and the parents split, with one doing the enrollment the first time and a differnt one enrolling the same child the second year. how could i track a family that changes its basic identity? I guess I need a date of enrollment and another id field for the family, but should it be related to the first ID or totally separate? could i relate them like ID 1 and ID 2 for that particular family? the KID remains the same but the parent/house contact may change entierly.

2) with constantly changing contact information for a family, since many of the clients are very transient, we'd have to track the contact data over time, and probably keep a historical file of all the prior info, so the contact table would have to be date based as far as pulling up the current stuff. does that make sense or am i being unnecessarily complicated here?

[Ender]

You're not being unnecessarily complicated. What you're describing is real world data that will require a complicated solution to properly track it. My Child-Enrollment system is around 30 files with tons of fields, scripts, and layouts.

You might hire a developer to get the basic structure built for you, then you can continue to add things as needs arise. If you are determined to do this yourself, take FM training courses and continue to learn as you develop.

[LaRetta]

The KID is what needs to be tracked. And this can get complicated, Nancy. Services provided, eligibility, etc. is usually based upon the KID address (and other criteria) within date spans so I suggest you have an Address table to track the KID movement. It would contain KidID, kid address, ParentID and start date. The ParentID (primary person responsible) must be based upon one PERSON. If that ID was originally attached to dad and the family splits, a new ParentID should be created for mom if the kid goes with mom. This will ensure tracking by allowing dad

[Ender]

In my solution, it made more sense to put the address information in the Family table (a family record is people living together at the same address.) This way if the family moves, the address only needs to be changed in one place.

If a family separates, usually the children stay together with one of the parents. If the children are separated and still attend the program, another Family record must be generated and the new Family ID must be attached to the old children.

[LaRetta]

I mostly agree. However, if the address most applies to the KID and the address is attached only to the kid, it's only entered in one place. The Parent address can then be accessed from the Address file (on ParentID) - also depending upon date. Which parent was responsible 6 months ago? With the populations I've worked with (mental health), the children are frequently split between parents. Attaching the address to the KID therefore makes more sense from my perspective. One Kid, one parent living here ... another Kid, another parent (or guardian) living here, etc.

There are many ways to go here ...

[nancyc]

yes, i see the complexities, and i agree, the KID is the crucial component for this particular file, the family or parent is not the one receiving the services, they are directed at the child, not the adults, so tracking the KID in this case is what makes the most sense. which means i now have to go back and assign each KID a unique ID when i thought i only had to do that for the FAMILY originally. oh well, live and learn.this is gonna be a lot of fun, we're talking like 3000 kids at this point. sheesh. so as far as assiging IDs maybe a K and numbered series after the K of 00001 and so on? like wise for the parents, a P and OOOO1 and so on, and for the original service providers, I have a site number that is already assigned and unique, thank goodness for that at least. So, I take the KID file and relate it to the PARENT by date, home location, and relate it to the PROVIDER by times and days served at a particular PROVIDER site. how do i show a KID is related to another KID (an enrolled sibling) to ascertain if schedules are similar? would that be the link through the PARENT? group kids by parents to show relationships...then i would need to compare times kids attended with times the provider was supposed to be providing services and see if those match, and then compare times kids were found to be not present to make sure the provider was not reimbursed for or claiming services were provided then, based on a time and kid and date comparison for a monitoring visit. hm....just thinking about how to make these relationships as simple as possible. the problem is they all need a historical record content because they need to be tracked over time, monthly, to show attendance and service patterns. so each month has to have its own field and attendance has to be tracked that way, it seems...so i would take a month and kid as a two part field and mark the kid as present or absent for each day of the month....with 3,000 kids this could take forever just to enter tht data...maybe a summary field instead called 'perfect attendance' could cover all the kids who are there every day, and kids who are not there every day could just get a number of days present versus absent for he month, not have to mark each day individually....and a total of days attended for that month versus days they could have attended....sorry, thnking out loud here again...so the data tables can all be relatively small sets of fields...question, does every data table to be linked to anoehr need to have the KID, PARENT and PROVIDER ID in each set of tables?

[Ender]

nancyc--please break up your text into smaller paragraphs on future posts...It's too hard to read the big blocks.

Each file should have its own ID numbering. It's not really important if they start with a letter or not; that could be useful to users but the database won't care (so long as they are defined as text.) You know you have siblings because they share the same Family ID.

That's as far as I could get.

[LaRetta]

Nancy said