DB diagram

hi guys ,

the following image showing Tables relations for billing system

  • customer can take many service

  • service can take many promotions

-i bill customer every month , 3 months ,6 months , year

  • maybe customer get discount 50% in first three months … this is example of promotions

etc …

  • are the relations between DB tables correct ?
  • credit , charge and balance in correct table ?
  • names of columns is it correct ?

please help me , am some lost

regards

I am not sure your records for past billing won’t be correct if prices change over time.

This should be recorded at the time of purchase.

What was the service charge a tthe time of purchase?

Can’t work out whether you are doing that.

I would put username and password in the customer table.

I would also use an email address as the user name, because they are unique and people don’t forget them.

You don’t need a separate id for the linking tables customerservices or customers_promotions, as there is a natural joint key in each case - customer_id with service_id in the first table and promotion_id with customer_id in the second table. Unless you declare these combinations unique, someone can sign up for the same promotion twice, for example. However sometimes a separate id is used to make further linking to another table easier.

Next, you have some attributes in one table that are in the table they are linked to. This creates lots of room for errors.

Accountdetails has an id of its own, then includes customer_id and service_id again - they are in the customerservices table already. If you use a separate id for customerservices, you do not need to have customer_id and service_id in accountdetails. If you use a joint key in the customerservices table, then you would use customer_id and service_id as the joint foreign key in accountdetails.

There is a balance in customer services and in account details - are they the same thing?

So re-think things a little bit and have another go. Any changes depend on whether you use a joint key or not in some tables.

EDIT Ah I too wondered about the username and password being in the other table, then forgot it.

i take ur notes , find bellow the diagram please

about user name an password , user name and password will be in another table for radius

how about now ?
can i work on this diagram , i’m afraid to face any problem cuz of this diagram later , any other suggestions ?

ur help appreciated

i cant set user name and password in customers table , cuz every customer maybe will have many services …

i’m waiting ur help