What is the simplest way of implementing pricing plans in mysql and php?

I am working on a project, and I am trying to find a way to associate my pricing plans table with a customer.

id  name    description      price  days
1   Free    the free plan    0.00   0
2   Silver  the silver plan  49.99  365
3   Gold    the gold plan    99.99  365

My first thought was to add the plan id as foreign key to the customers table. But i also need to know when is the expire date (based on the purchase date and the days of the plan selected).

Answer

When attempting to do something like this, it is best to keep tables abstracted from each other.

user_pricing_plans  - the table name
pricing_plans_id - the id for the record you want to associate with a user
user_id   - the id of the user
date_expiration - the date the plan will expire
date_purchased - the date the plan was purchased

following an approach like this will allow you alter this table if needed to add extra information. you could also follow a similar approach in the future with another table.

the key to all of this is separating your concerns ( the data ) into separate containers.

Edit: to kind of hint at what I’m getting at about why separating the tables is a good idea, I added the date_purchased as a field in the user_pricing_plans table.

a dba I know once said that “mysql is not a place for developers – they try to create tables to work with their code. tables are meant to represent data. your code should be written to work with the data and the schemas that represent them – not the other way around”