design issue

[BadKitty]

the database i'm creating will be used to store city issued permits. there a 4 different types of permits that can be issued, some w/the same info (i.e. contractor, owner) and others with different data (certain permits are only issued for a specific # of days, others require a tap or meter size, etc.). i have separate tables for the contractors, owenrs, and payments, but each payment needs to be associated with a particular permit #. for example: check #3 from john doe contracting is associated with permit # m123, or h123, etc. that's why i decided to have a separate table for each permit type. i was attempting to avoid data redundancy. i also need separate forms for the users to enter data into as per their request. i'm open to suggestion if you have some other ideas. thanx in advance.

 

[Pat Hartman]

There should be a single table for permits. If the permits have wildly differing attributes, you may need some extra tables which will be related 1-1 with permits so that each permit type can have a table for its special attributes. Think long and hard before creating these 1-1 tables because they should not be used unless absolutely necessary. make sure that all common fields are stored in the permits table. All relationships will be made to the permits table. No relationships will exist with the specific permit tables except for their 1-1 relationship with permits. The upshot of this type of structure is that all permit numbers are assigned from the same series in the same table. This will make all your other work easier and you will be able to enforce referential integrity.

As to the separate forms, I would have a main form with the common permit data and a tab control with a page for each permit type to hold that permit's specific data attributes.

Contractors and Owners belong in the same table since the sets of data may overlap. Contractor and Owner are simply roles a company/person plays in a particular transaction.

 

[Pat Hartman]

PS, several of your posts have been closed because you keep posting duplicate questions. Organize your thoughts and pick a suitable forum. Then stick to a single thread until you have a workable solution.

 

[BadKitty]

primary key question

the permits are working out well...thanx for your help! i do have one other question though...when i had the owners & contractors in seperate tables, i used an autonumber as the primary key for owners & the license # for the contractors (i'd stilll like to use the license # for look-up purposes). also, at times the owners need to be associated with particular contractors...how would i do that in the same table?

 

[Pat Hartman]

Use the autonumber as the primary key but include the licenseNum as a separate field. Make its default null and not required. Create a unique index on it that ignores nulls. The index will prevent duplicates on the licenseNum value.

 

[BadKitty]

thanx!

thanx for all your help...i should be good to go now. i appreciate it (so will my boss)! lol.

 

[BadKitty]

a bit out of practice

probably a stupid question, but...do those extra tables related 1-1 w/the main permit table need a primary key also, or just the contractor/owner id as a foreign key?

 

[Pat Hartman]

ALL tables will have a primary key in a properly constructed database. In order for Access (Jet) to recognize a 1-1 relationship, two tables must be related on their primary keys, so by definition, the related tables need to use their foreign key as their primary key. Don't forget, the foreign key is defined as long integer. It cannot be an autonumber.

 

[BadKitty]

redesign

same database (city permits), but i'm trying out a couple of differnt things now & just wanted to be sure of something...
1 have 3 tables contractor/owner, permits, and payment. permits are 'orders' and payment is 'order details'.
given contractor/owner has a one-to-many relationship w/permits. obviously there must be a relationship between permits & payment, just not sure what type. and once that relationship is created, is it necessary to create a direct relationship between contractor/owner and payment? i'm confused b/c any particular permit must be related to a contractor, and a specific permit must relate to a payment, so is contractor/owner indirectly related to payment?
i need to have a query return payments make by a particular contractor, and also pull up the payment for a particular permit, or payments for particular types of permits (i'm going to use a query to concatente permit type & permit# so it'll be like h####, m###, etc).

 

[Pat Hartman]

You need to talk more to the users about the relationship between permits and payments. Do they accept partial payments so that a permit may have many payments? Does an individual payment apply to more than one permit so that it has to be properly split? Can an individual payment apply to permits pulled by more than one contractor/owner?

 

[BadKitty]

some more info...

first of all, happy new year! & good luck in '06. hope all your holidays were happy ones for you & your loved ones!

k, so i've come to some conclusions based on what you asked me in your last post...

• no - partial payments are not excepted.
  
• regarding an individual payment applying to more than 1 permit...on a very rare occasion this may occur, but since it is so uncommon i don't see that option being a necessity - the users can seperate them themselves (ie - they can create 2 payment occurances w/the same check# for different permits, right?).
  
• and no, 1 payment cannot apply to permits purchased by more than 1 contractor.

based on the very vague specs i continue getting from my users (believe me...i've tried to push for more definite info.). every single time the issue has been discussed it's like pulling teeth to get answers & damn near impossibe to get them all to agree - so i've decided to take the simplest route for my own sanity.

i do, however, as i mentioned before need to run queries & base reports on contactors (how may permits they purchased, what kind, etc.), and also pull info for all hydrant permits, or meter permits, etc., pull info by date of issue - which would be payment date, and in some cases, pull particular payments (based on either contractor, permit, or possibly even check#). i am primarily concerned w/the first 2 reports i mentioned.
thanx in advance (again...you've been a lifesaver.