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

Posted on 2011-10-23
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?

Question by:hej613
    1 Comment
    LVL 11

    Accepted Solution

    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 :

    <any other needed for individual dog/bitch>

    dog_id (linked to tbl_Dogs)

    <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 !)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now