Solved

How to create and manage a MySql database to solve a simple FK problem

Posted on 2013-05-13
5
373 Views
Last Modified: 2013-05-14
Hi all!

I am trying to build a small app to understand a bit of programming but I'd need a bit of help to understand how to correctly arrange this:

TAB_CUSTOMERS
- id_customer   // primary key, autoincrement
- name // a varchar
- group // a varchar


TAB_PAYMENTS
- id_payment    // primary key, autoincrement
- month  // a varchar
- year
- id_customer_fk // foreign key reference to a customer (TAB_CUSTOMER)
- paid // int with money paid

I have prepared a page where the user can change the year and click on months to change the state to paid or not paid.

Is my database correctly designed?

What kind of action should I take when I insert a new user?

I don't know how to handle the payments table...

In my idea I could automatically create a few years of future use for every new user inserted and set all the months' payment to zero.

But I think that there must be better ways and I'd like to ask experts so I learn the best way to handle this.

I am not really looking for code (even if it's welcome), just for problem analysis and ideas.

Thanks!
0
Comment
Question by:ltpitt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 39163173
A couple of ideas

1. You probably don't want to enter default payments of 0. You can use the lack of data to imply that no payment was made

2. I would probably want to record the exact date of payment in one field, instead of separating it into month and year. You can do some more logic later to figure out if the payment was made on time.
0
 
LVL 1

Author Comment

by:ltpitt
ID: 39163212
Thank you for answering!

1. Sounds ok to me!
2. Agreed

Still I have the biggest problem: what should I do when I add a new user?
Simply consider no payments made at all, right?

How should I handle the new client's insert?

Simply fill the client table and just insert nothing in payment table?
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 39166097
exactly.

when you have a new user, you enter that user into the user table and do nothing more. When they start giving payments, you enter those payments into the payments table. Ultimately, you will have a query which looks something like this

SELECT c.name, c.group, p.payment_amount
FROM TAB_CUSTOMERS c LEFT JOIN TAB_PAYMENTS p ON (c.id_customer = p.id_customer_fk)
GROUP BY c.name, year(p.payment_date), month(p.payment_date)

Open in new window


which will show you all payments for all customers, grouped by month/year.
0
 
LVL 1

Author Comment

by:ltpitt
ID: 39166438
This is very helpful and interesting: the solution I was looking for.

So when I insert a payment in that moment I should relate it to the customer's id and, using a CASCADE option I can be sure that when I delete a customer also all his payments get deleted too (I hope this is a good idea).

I'm off to learn a bit of sql to understand better the query you suggested: thank a lot :)
0
 
LVL 1

Author Closing Comment

by:ltpitt
ID: 39166442
Guided me kindly to understand the problem and also suggested some code: what else?
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

724 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