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.

 

[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?

 

[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?

 

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

 

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