Help with db structure

[cwcrogan]

Hello,

I am hoping someone can point me in the right direction. I am trying to develop a permitting solution for the Fire Dept I work at. We issue 14 different types of permits; two of which have 4 classifications each. Each type has some similiar information and each has unique information to collect as well. I would like to set things up so that the Fire Administration could change the pricing on permit type as needed. Each permit type also has a different look when printed (due to the unique information).

I thought I was on the right track by setting a table to store each permit type, it's price, pertinent information (for printing), etc. and a second table for the actual permits. I thought I could either alter the layout for the type of permit selected or use calculations to display only the fields needed for that particular permit. It's my understanding that the print layout should not be used for the data collection, which is fine - I could just script a print.

Then, later, I thought it might be better for each type of permit to have its own table, but I question the ability to easily generate reports on all permit data.

In the users ability to make price changes, I'm having trouble with the two permit type that have the 4 subcategories. I'm drawing a blank on how to go about this. For example, smoke and carbon monoxide detector inspections vary in price depending on the number of families (units) in the building (1,2,3-5,>5). Do I just have 4 permits with similiar names? I'm sure there is a more elegant solution, but I can't figure it out.

Thank-you in advance for any help or direction.

Edited October 10, 2006 by Guest

[CoZiMan]

Hi,

Maybe a basic concept discussion is the answer, here.

You discuss 14 permit types with an additional 8 separate sub classifications.

Here is how I would set this up, to start.

Create a table that has two fields.

First- Permit Type

Second - Permit Sub Classification

Now fill in a total of 20 records, 8 of which have a sub-class entry. (Notice that 12 (subtract the two with sub classes) + 8 = 20)

This gives you a UNIQUE Record ID = Type + SubClass

Now create a table that has the requisite fields for the permit application.

Then add a field for the Record ID using a value list from the first table.

Viola, every permit has a classification and this will allow you to create an output form for each classification.

There are probably many other fields and entries that are UNIQUE for the Permit-Subclass and you can add these as you go.

Make sure that you seperate the UNIQUE permit info from the individual permit info and the results should be good.

I (and others) would be happy to work with you from a sample file.

TTYL

[cwcrogan]

coziman,

Thank-you for your response and sorry for the delay in my response. I have been working on a sample file and will upload when appropriate. Your directions are clear cut and helpful. I'd like to put aside the idea of different pricing for one type of permit for a moment and deal with the main structure.

As I was working on this, I've discovered that the different types of permits[1] have many more different field requirements from one another than they do common fields. In fact the common field may only number 3 or 4 (date, name of applicant, location, ID). So I'm wondering is it right to contain all the permits in one table (and the permit types and related info in another)? In the same question, is it okay to have many unused fields in every record, or does this add unnecessary file size? Would it be better to structure it differently - a table for the types, another for permits issued (with the common fields), and then more tables for each type of permit containing only the field necessary for that type? And, if this is better, how would the relationship work?

I hope that I'm making sense here. I want to approach this correctly. I've read a couple of books, visit a few forums, read mags including Matt Petrowski's video series, but I haven't yet grasped db design. I think what I'm missing is experience.

[1]What our department collectively calls permits are actually permits, certificates of inspection, and reports (if it matters)

[CoZiMan]

Hi,

Glad you are still plunking away at it !

Good that you are seeing the requirements differently than before. That is part of the 'experience' you need to take this further.

'When we stop learning, we're probably deceased.'

(Don't know who said that or if it's a paraphrase)

On to your question about the blank fields, etc.

In my experience, it's not a problem to have blank entries for fields.

As a matter of fact, it may be beneficial.

I've generated several entry-form screens where the display changes based on the type of form or access level of the user. Both of these occurences allow for incompleted field entries.

So in your case, it would depend on how you want to be able to aggregate the data LATER, in the reporting phase. Similar structures make for much easier aggregation, and vise-versa.

I say use the same structure (where logical) with the PROBABILITY of seperating the commmon elements into a 'customer' data file. This way you can associate different forms with the same person. This will REQUIRE the use of a unique ID for the customer. I usually use LastName|PhoneNumber|CreatedDate or some such calculation text field. Seems like a difficulty, but there are a lot of John Smith's out there!

You may even find that the documents you've classed differently really aren't different at all, they just appear to be.

Another item to watch for is naming conventions for fields you'll use on the different forms.

This can help you when you go to generate scripts, etc.

Also, be sure you know what you are doing if you use repeating fields. They seem simple but can complicate matters considerably, or simplify them!

I'll check back on this thread regularly.

:)

[cwcrogan]

Coziman (and others),

I've reached the point where I'm literally pulling my hair out and losing sleep over this. I've been working this out mostly on paper and keep going around in circles.

I've gone and convinced myself that the above route is not the best way (give me a dope slap if I'm wrong - please). I started out with the table for all the permits and types (with fees, etc) in a second table. I understand the relationship and how it works (rather simple, really). But what I can't accept is the fact that there would be over 200 fields in the permit table to contain the info for all the different types of permits. Some types only require a dozen or so fields, but others (ie fuel tank removal) require many, many fields. And most of the fields don't overlap from permit to permit.

I keep going back to a table for each type of permit and then get stuck on how to report collectively on them. For instance, if I wanted to see how many of each permit type was issued (or applied for) for a given day, week, month, or even year how would I do that? Is there a way to have one table keep track of 14 others?

If you think that the one table with over 200 fields is still best, please hit me over the head with something very large and heavy...

[cwcrogan]

Alright, I'm back to reality. I'm using the two tables. This post pushed me back:

http://www.fmforums.com/forum/showtopic.php?tid/181499/

I can probably reduce the number of fields to around 100 if I use them more generically. So, all is well there.

Now, I'll have a different layout for each type of permit (showing only the related fields). On any of these layouts I'll be showing a portal of a self join table. The reason being is for the user to be able to see the recent permits of the same type. I'd like to place a button over the portal row that would change the current record to be the one in the portal. How can I go about this?

Edited October 25, 2006 by Guest

[Ed Berkel]

I too work for a fire department and we have used FileMaker for our permit system since version 1. Things have improved considerably. I'm currently rebuilding our system in FM 8.5 (currently running 5.5). You need one table for your actual permit data entry and a second table for your fee structure. I created all our permit types years ago and they are a bit clunky, but they work. I then do a lookup from the fee table to insert the appropriate fee in the main table. I have a relationship from the main table to the calculated permit type in the fee table. I use that for a value list in the main table to make sure that the permit type is correctly entered and the look up works correctly.

If you would like a copy of the system, contact me at [email protected]

I will create an unlocked clone and send it to you.

Ed Berkel

Fire Marshal

Mehlville Fire District

St. Louis, MO

[cwcrogan]

Ed, that is most generous of you. I have sent you an email. Thanks!