SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: DB diagram

  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DB diagram

    hi guys ,

    the following image showing Tables relations for billing system

    billing_diagram.JPG

    - 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

  2. #2
    SitePoint Evangelist tangledman's Avatar
    Join Date
    Sep 2005
    Location
    Puerto de Mazarron, Murcia, Spain
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i take ur notes , find bellow the diagram please

    billing-promotions.JPG

    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

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tangledman View Post
    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.
    i cant set user name and password in customers table , cuz every customer maybe will have many services ...

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2008
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm waiting ur help


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •