Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Generic flat file template

    We're needing to ask all counties in our state to provide a list of addresses that they have jurisdiction over. One of the pieces of information that will be included with each address is a listing of permits that can be licensed for that location.

    We do not know how many types of permits will ultimately be available in this system, as they are constantly changing. So, we'd like to come up with a template for asking for this info which can compensate for this unknown total number of permits.

    We have 2 ideas, but are wondering if there's another:

    -Have a bitwise field, just make it 40 characters long. Each position is designated as a permit type, and a 1 in that position means that the permit is valid for that address:
    Code:
    0001100101000000000000000000000000000000
    -Similar, but actually come up with a standardized name for each permit type, and allow for a comma-delimited list of entries (in a tab-delimited file):
    Code:
    ELECTRICAL,MECHANICAL
    We are purposefully avoiding asking for a separate record for each permit type.

    ---=Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >We are purposefully avoiding asking for a separate record for each permit type.
    Violate 3rd Normal Form at your own subsequent processing peril.
    If you implement either alternative in your original post, please post sample SQL how you'd select all locations which have any THREE enumerated permit types.

    I you really want to have a condensed data representation, you could base it upon a binary number format.

    Permit1 = 1
    Permit2 = 2
    Permit3 = 4
    Permit4 = 8
    Permt5 = 16
    Permit6 = 32
    etc.

    Any combination of Permits is just the arithmetic sum of the values to the right of the equal sign.
    Again, querying against this value can be a challenge.

    HTH & YMMV
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Wow, I really thought I'd pegged the description well after I read it over yesterday. Here's my clarification, and sorry for any wasted time anyone thought about this:

    So, I'm talking about the file format that we want counties to FTP to us, that we'll then load into the database. We're trying to avoid adding columns to the file template as more permit types arise. So, our first thought was, "How can we compress multiple columns worth of information into one." It's possible that some counties will add to the number of permits they'll license, while others may only license the original one's they started with at the outset.

    When we process the file, we will normalize the data.

    And the more I think about it, maybe we are increasing the complexity for the counties by asking them to denormalize data for this FTP file. I think I need to talk to them more about how they're actually storing their data (which could be different in every county).

    ---=Chuck

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Here is more food for thought....
    How do you reconcile the data when different counties use different name for "the same" type of permit?
    How do you reconcile when different counties use the same label for different permits?
    IMO, somebody needs to codify Permit naming convention & all parties must agree to adopt & use the same naming convention.
    The same is true for column names within a single database application.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Right, we're on the long road to standardization. This particular issue was just really stumping me yesterday. Maybe it's not as tough as I thought. --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •