[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
1
Medium Priority
?
337 Views
Last Modified: 2012-08-13
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
Comment
Question by:hej613
1 Comment
 
LVL 11

Accepted Solution

by:
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
dog_id (linked to tbl_Dogs)
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question