database structure

  • Thread starter Thread starter Rachel
  • Start date Start date
R

Rachel

I am hoping someone can help me with a query I have regarding a
database structure I am working on, and if there is any way to improve
what I have created. Basically, I need a solution that records pets,
treatments assigned to pets, and subsequent invoices for the
treatments. Pets can have more than one concurrent treatment, and
treatments can have several invoices.



I have created 3 separate tables (Pets, Treatments, Invoices) each
with a primary key being an auto-generated ID number. The pet_id is a
foreign key in the treatment table, and I have a one to many
relationship set up. The same goes for treatment_id in the invoices
table.



Can anyone think of a better way to do this using an ADP front-end to
a SQL Server database?



Many thanks.
 
That seems reasonable providing that there's a possibility of multiple
invoices for each treatment. If not, then I'd set up a one-to-one
relationship there. Otherwise, you're on the right track.


Rob
 
That seems reasonable providing that there's a possibility of multiple
invoices for each treatment.  If not, then I'd set up a one-to-one
relationship there.  Otherwise, you're on the right track.

Rob







- Show quoted text -
Click to expand...

Thank you so much for your opinion, much appreciated!
 
That seems reasonable providing that there's a possibility of multiple
invoices for each treatment.  If not, then I'd set up a one-to-one
relationship there.  Otherwise, you're on the right track.

Rob







- Show quoted text -
Click to expand...

How would it be best to insert and then update records?

Would subforms be the best way to do this? As I would need to be able
to add new animals, add new treatments for new or existing animals and
add invoices. I would also need to have a running total for continuing
treatment.

Thanks
 
Rachel,

I think you have it simplified too much...

Can a person have more than 1 pet?
If so, you need a table for Owners and a table for the cross-reference
between the owners and the pets.

Is the pet paying the invoice?
Of course not, that is another reason for the Owners table. Otherwise,
you are repeating the information in the invoice.

Can a treatment be multiple visits to get your running total?
If so, you need a visit table linked to the treatment and to
the invoice. That is assuming that a person can pay an invoice for a
visit, but that it may not be a "total" treatment.

Can an invoice be fore more than one treatment?
If so, you need an invoice/treatment cross reference table to hold
that
relationship.

The running total you asked about is a reporting thing.

If you want this designed correctly, contact me off list and I will
help you with the DB design. ([email protected])

Robert
 
Rachel,

I think you have it simplified too much...

Can a person have more than 1 pet?
If so, you need a table for Owners and a table for the cross-reference
between the owners and the pets.

Is the pet paying the invoice?
Of course not, that is another reason for the Owners table. Otherwise,
you are repeating the information in the invoice.

Can a treatment be multiple visits to get your running total?
If so, you need a visit table linked to the treatment and to
the invoice. That is assuming that a person can pay an invoice for a
visit, but that it may not be a "total" treatment.

Can an invoice be fore more than one treatment?
If so, you need an invoice/treatment cross reference table to hold
that
relationship.

The running total you asked about is a reporting thing.

If you want this designed correctly, contact me off list and I will
help you with the DB design. ([email protected])

Robert







- Show quoted text -
Click to expand...

many thanks for your offer
 
Back
Top