locked
How to design RRS feed

  • Question

  • Hello All,

    I want to design a database for invoice and billing management of a customer.

    can anybody help me in resolving this database design issue.

    Scenarios will be.

    1.Every monthly the bill needs to be generated against a service provided to the customer.

    2.Customer needs to pay the bill with a due date if due date passed the first fifteen days the surcharge amount needs to be payed by customer with 2% extra charge daily. the next fifteen days the 4% extra amount to be paid by the customer , both extra amount will be on initial bill amount only.

    3. if the bill is not filled then the next bill amount will be added to the previous bill and on the same basis the bill will be generated.

    There needs to be flexiblity of the waive off the charges as well.

    it's complicated for me but for you guys it will be much easier.


    • Edited by EmpAnsar Wednesday, January 11, 2017 6:43 AM
    Wednesday, January 11, 2017 6:42 AM

Answers

  • Hello,

    I agree with Sunil. I you let others design your database, then you may not understand the design completly and in future it will be hard for you to make design changes.

    If you are not that firm with database design, then you can find here http://www.databaseanswers.org/data_models/index.htm tons of examples to give you ideas, there are also several examples for ERP, invoicing, service providing etc.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 11, 2017 8:00 AM
    Answerer
  • See the examples

    http://www.databaseanswers.org/data_models/invoices_and_payments/index.htm


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 11, 2017 8:02 AM
  • Ok, Just you give you a starting pointer; you can create a table like below which maintain all the information out of the hard copy of invoice. You want to store all the details from actual invoice to this table and create as many columns. You might ended up creating multiple table and relationship between those table.

    I hope this will help you to start with.. 

    Create Table InvoiceDetails
    (
    InvoideId Int primary key clustered,
    InvoiceNumber varchar(200),
    InvoiceDate datetime,
    CustomerName varchar(200),
    CustomerAddress varchar(500),
    InvoiceAmountTotal float,
    PaymentDueDate datetime,
    ISPaymentDone bit,
    Comments varchar(255)
    )


    Kindly mark the reply as answer if they help

    Wednesday, January 11, 2017 9:25 AM
  • Hi,

    Just to help you kick started with the database design for your application, I have attached a database diagram for your reference. It's just a starting point and I haven't considered your business rules here. Should you need assistance, feel free to get in touch.

    Feel free to normalize it. All the code logic can be managed through stored procedures or views that's because I am not a big fan of manipulating the raw data and storing it into the database table.

    Cheers,

    Sunit

    Thursday, January 12, 2017 2:36 AM

All replies

  • We can help you with any error or technical issue you getting. As far database design in concerned you have to figure out yourself, what entities (Tables) will be there and what are the properties (columns) of those entities. Probabaly to start with you can sketch down a design on paper and later create those tables in SQL Server using SSMS and setup relationship between those table.

    So the requirement of applying additional surcharge on late payment, that you can do pragmatically if payment  date has lapsed the days.

    Thanks,



    Kindly mark the reply as answer if they help

    Wednesday, January 11, 2017 7:38 AM
  • Hello,

    I agree with Sunil. I you let others design your database, then you may not understand the design completly and in future it will be hard for you to make design changes.

    If you are not that firm with database design, then you can find here http://www.databaseanswers.org/data_models/index.htm tons of examples to give you ideas, there are also several examples for ERP, invoicing, service providing etc.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 11, 2017 8:00 AM
    Answerer
  • See the examples

    http://www.databaseanswers.org/data_models/invoices_and_payments/index.htm


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 11, 2017 8:02 AM
  • Thanks for all of your replyies.

    what i am thinking.

    a Customer will be given a Service

    But before service he will be given a quote against the services to be given to him.

    After he agrees to the Quotation an Order will be placed against that Quote. after the quote the invoice will get generated .

    Now at the time of invoices i am confused how the bill as a hard copy given to the customer and same needs to be maintained within the database.

    I need your help in billing module.

    i am not getting it , at least a pointer to this will help me a lot.


     

    Wednesday, January 11, 2017 9:14 AM
  • Ok, Just you give you a starting pointer; you can create a table like below which maintain all the information out of the hard copy of invoice. You want to store all the details from actual invoice to this table and create as many columns. You might ended up creating multiple table and relationship between those table.

    I hope this will help you to start with.. 

    Create Table InvoiceDetails
    (
    InvoideId Int primary key clustered,
    InvoiceNumber varchar(200),
    InvoiceDate datetime,
    CustomerName varchar(200),
    CustomerAddress varchar(500),
    InvoiceAmountTotal float,
    PaymentDueDate datetime,
    ISPaymentDone bit,
    Comments varchar(255)
    )


    Kindly mark the reply as answer if they help

    Wednesday, January 11, 2017 9:25 AM
  • Hi,

    Just to help you kick started with the database design for your application, I have attached a database diagram for your reference. It's just a starting point and I haven't considered your business rules here. Should you need assistance, feel free to get in touch.

    Feel free to normalize it. All the code logic can be managed through stored procedures or views that's because I am not a big fan of manipulating the raw data and storing it into the database table.

    Cheers,

    Sunit

    Thursday, January 12, 2017 2:36 AM