How to do a payments table - am I on the correct track?
Posted on 2011-10-23
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?