Click here to Skip to main content
15,886,963 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
its basically a service project(i.e. bike insurance,telephone bill,credit card bill) all these bill details and amount will be given to a small company., that company will pay amount to concern things.

here i have to create table for enter these services.

ex
services_name id

bike insurance - 1
credit card details - 2
telephone bill - 3

then

id services_details

1 company_name
1 vehicle_no
1 amount
1 amount_given_date
1 last_date_to_pay


in services_details client will enter dynamically as per services_name requirement
Posted

I am not sure if i understood your question correctly. I am assuming you want an idea on how to structure your tables for your service project.
It would actually depend on the requirements and what data is required.
Given the description of the project details one approach would be,
You can have a table with the list of all services something like
SQL
CREATE TABLE tblServiceList
(
	ServiceID INT CONSTRAINT PK_tblServiceList PRIMARY KEY,
	ServiceName VARCHAR(100)
)


Then you can have a table that holds a list of all the companies for which you take payments or provide other services to that company's customers.

SQL
CREATE TABLE tblCompanyList
(
	CompanyID INT CONSTRAINT PK_tblCompanyList PRIMARY KEY,
	CompanyName VARCHAR(100)
)



You may also need to track the status of a particular service (Like is the credit card payment forwarded to the bank etc). So you can have a table that will list all the statuses in your workflow.
SQL
CREATE TABLE tblBillStatus
(
	BillStatusID INT CONSTRAINT PK_tblBillStatus PRIMARY KEY,
	StatusDescription VARCHAR(50)
)

--Some sample status that can be in your workflow.
INSERT INTO tblBillStatus
SELECT 100, 'Payment received from Customer' --Your client has received a payment from a customer.
UNION
SELECT 101, 'Payment forwarded to company' --Your client has forwarded the amount paid by customer to the comapny
UNION
SELECT 102, 'Payment confirmed' --Received confirmation from the company that they have received the payment your client forwarded.



There will be another table to hold the service details requested. Here all the bill payments or any other service requested will be stored.
SQL
CREATE TABLE tblServiceDetail
(
	ServiceDetailID INT CONSTRAINT PK_tblServiceDetail PRIMARY KEY,
        CustomerName VARCHAR(100),
	CompanyID INT NOT NULL CONSTRAINT FK_tblServiceDetail_tblCompanyList FOREIGN KEY REFERENCES tblCompanyList(CompanyID),
	ServiceID INT NOT NULL CONSTRAINT FK_tblServiceDetail_tblServiceList FOREIGN KEY REFERENCES tblServiceList(ServiceID),
	BillStatusID INT NOT NULL CONSTRAINT FK_tblServiceDetail_tblBillStatus FOREIGN KEY REFERENCES tblBillStatus(BillStatusID),
	InvoiceNumber VARCHAR(50),
	AccountNumber VARCHAR(50),
	BillAmount DECIMAL(14,2),
	DatePaid DATETIME,
	DueDate DATETIME,
	DatePaidToCompany DATETIME
)


This is just a sample approach. Your tables may have more columns based on the requirement (Example: You may want to store the company address and contact information in you company table). Also your tables may have to be structured differently depending on the client requirement.
Hope this helps you in getting an idea of how to design your tables.

Good luck :)
 
Share this answer
 
Comments
Prasad_Kulkarni 3-Sep-12 2:46am    
Good work TR, +5
__TR__ 3-Sep-12 2:50am    
Thanks Prasad :)
And? You need to try out and share what you did and got stuck. Posting just the question/requirement is not going to help.

Here is what is expected of enquirers:
1. TRY first what you want to do! You may find that it's not that hard.
2. Formulate what was done by you that looks like an issue/not working.

Try them and tell if you face issues.
Members will be more than happy to help like this.


For now, create 3 tables:
TABLE1: MstServices - SID, Name
TABLE2: TrnConsumer - CID, Name, Number, Amount, AmountDate, LastDay
TABLE3: TrnConsumerPay - TID, SID, CID
 
Share this answer
 
v2
Comments
Umapathi K 2-Sep-12 0:54am    
i am new to ms-sql server . just i am expecting a thread not entire things
Sandeep Mewara 2-Sep-12 0:56am    
Provided the start...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900