asked on
How to create and manage a MySql database to solve a simple FK problem
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!
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!
MySQL ServerWeb Development
Last Comment
ltpitt
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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 :)
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 :)
ASKER
Guided me kindly to understand the problem and also suggested some code: what else?
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.