I am using SQL Server 2005 for a project written in VB.NET.
I think I've been too close to this project and I am unable to see the obvious so I am looking for some db design help.
My project is a billing application. I have a CLIENT table and in this CLIENT table I have the normal items
- Client_Number
- Client_Name
- Address
- City
- State
- PostalCode
- Telephone
- Timestamp
I have a SCHEDULE table with the following:
- Schedule_Number
- Client_Number (FK)
- Rate
- StartDate
- EndDate
- Timestamp
So there is a one to many relationship between the CLIENT and the SCHEDULE. A Client could have many schedules.
I need to handle payments from the Client for the Schedule, so I need to create a table (Lets say PAYMENTS) that would allow me to hold the date a payment was made and then a quick way to do a 0-30 day, 31-60 day, 621-90 day and 91+ days for billing at the bottom of an invoice.
Maybe this is just too ridiculously easy but it seems I should just create a PAYMENT table with a FK to the Client_Number. The payment needs to always pay the oldest schedule so I wouldn't want to create a FK to the Schedule_No (or should I?).
Has anyone done this in the past? This is likely far easier than I am thinking but it doesn't seem too complex. I think I am writing primarily because it does seem easier than I thought.
Thanks for any insight.
fergmj
I think I've been too close to this project and I am unable to see the obvious so I am looking for some db design help.
My project is a billing application. I have a CLIENT table and in this CLIENT table I have the normal items
- Client_Number
- Client_Name
- Address
- City
- State
- PostalCode
- Telephone
- Timestamp
I have a SCHEDULE table with the following:
- Schedule_Number
- Client_Number (FK)
- Rate
- StartDate
- EndDate
- Timestamp
So there is a one to many relationship between the CLIENT and the SCHEDULE. A Client could have many schedules.
I need to handle payments from the Client for the Schedule, so I need to create a table (Lets say PAYMENTS) that would allow me to hold the date a payment was made and then a quick way to do a 0-30 day, 31-60 day, 621-90 day and 91+ days for billing at the bottom of an invoice.
Maybe this is just too ridiculously easy but it seems I should just create a PAYMENT table with a FK to the Client_Number. The payment needs to always pay the oldest schedule so I wouldn't want to create a FK to the Schedule_No (or should I?).
Has anyone done this in the past? This is likely far easier than I am thinking but it doesn't seem too complex. I think I am writing primarily because it does seem easier than I thought.
Thanks for any insight.
fergmj