[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# How to do a payments table - am I on the correct track?

Posted on 2011-10-23
Medium Priority
337 Views
good morning experts - I have just started learning PHP and wanted to design a database for my wife who is a dog breeder.  I have started to layout the table and wanted to see if I am heading in the correct direction.

Current tables are:

Customer (Name, email, phone, etc)
Puppies(table with specific information (Color, price, etc)
Breeding Dogs - information specific to our breeding dogs
Litter table - Stores dates a littler was born and from which dogs

My idea in the above is:
Litters/Puppies are connected via FK - I want to be able to track number of dogs in the litter date they were born, date of the vet check, and date they are able to go home.  In the Puppies table they will also have a "PRICE" column(These are huskies some are more expensive than other depending on rarity of the color of the dog)

Usually a litter of puppies is born, people call and make deposits on the dog and setup a time to come pickup the puppy after a certain number of weeks after the birth, and then come in and pay the remaining balance.

My first thought is just have a "Payments" table - linking the puppy / customer together with the amount of payment and type(Deposit, paying the balance, etc.)

I can do a calculation that looks at puppy price (puppy table) and the amount of payments received on that puppy and if it = 0, then the puppy is sold.  Does this sound like a logical setup, or is something going to come and bite me in the butt I haven't considered later?

Thanks.
0
Question by:hej613
1 Comment

LVL 11

Accepted Solution

maeltar earned 2000 total points
ID: 37014559
I'd be tempted to add another table...

Customer (Name, email, phone, etc)
Puppies(table with specific information (Color, price, etc)
Dogs - information specific to our breeding dogs

Litter table - Stores dates a litter was born, and id of breeding table..

Breeding (Table with id of dog and bitch, date of mating etc...)

So you have :

tbl_Dogs
id
name
kc_reg_no
<any other needed for individual dog/bitch>

tbl_breeding
id
date_of_mating

tbl_litter
id
breeding_id
<other litter info>

Using something on those lines you can very easily with simple queries and joins get the information out, it will also be easier to maintain...

I'd be more than happy to aid you futher on this at no charge, just go into my profile and use the hire me button (I have an interest in all things doggy !)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month18 days, 10 hours left to enroll