March 20, 2013 at 4:41 am
Hi,
I have a scenario according I have to create a table struture. As I have never designed any table structure based on a scenario of SRS. Plz guide in this scenario
Application will receive the voucher order from the printing agency.
1.The user will enter the transaction ID of the order.
2.User will enter the receipt date as current date.
3.User will verify each gift voucher with gift vouchers records in the database.
4.On scanning the Gvs, application will validate
Voucher ID
Voucher denomination
Voucher sales price
Time period validity
First Voucher ID
Last Voucher ID
5.In case of the discrepancy in the count of the vouchers, application to prompt with error
6.In case of short receiving, the transaction ID will be open to receive the remaining vouchers for the period of 1 month from order raised date.
7.In case of excess, the additional vouchers will be sent back to the agency operationally
8.All open transaction IDs should be closed automatically after 1 month
9.Ho to receive the entire order & raise the discrepancy to the agency with the help of log file.
10.Voucher once in warded, should get active in the database as to sell to the customers.
11.Voucher once in warded, without any discrepancy, the application to complete post goods received and save inventory of the vouchers.
According to my understanding I have created this structure... Is it ok or anything else is required?
Create table GV_ReceiveHO
(
VoucherNo varchar(20),
VoucherDenomination int,
FirstVoucherID varchar(20),
Last VoucherId vaarchar(20),
TimePeriodvalidity datetime,
VoucherStatus varchar(20)
)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 20, 2013 at 5:20 am
kapil_kk (3/20/2013)
Hi,I have a scenario according I have to create a table struture. As I have never designed any table structure based on a scenario of SRS. Plz guide in this scenario
Application will receive the voucher order from the printing agency.
1.The user will enter the transaction ID of the order.
2.User will enter the receipt date as current date.
3.User will verify each gift voucher with gift vouchers records in the database.
4.On scanning the Gvs, application will validate
Voucher ID
Voucher denomination
Voucher sales price
Time period validity
First Voucher ID
Last Voucher ID
5.In case of the discrepancy in the count of the vouchers, application to prompt with error
6.In case of short receiving, the transaction ID will be open to receive the remaining vouchers for the period of 1 month from order raised date.
7.In case of excess, the additional vouchers will be sent back to the agency operationally
8.All open transaction IDs should be closed automatically after 1 month
9.Ho to receive the entire order & raise the discrepancy to the agency with the help of log file.
10.Voucher once in warded, should get active in the database as to sell to the customers.
11.Voucher once in warded, without any discrepancy, the application to complete post goods received and save inventory of the vouchers.
According to my understanding I have created this structure... Is it ok or anything else is required?
Create table GV_ReceiveHO
(
VoucherNo varchar(20),
VoucherDenomination int,
FirstVoucherID varchar(20),
Last VoucherId vaarchar(20),
TimePeriodvalidity datetime,
VoucherStatus varchar(20)
)
going by the problem's items, here's what I see:
Item #1, what if two different users enter the exact same voucher number? don't you want that to be unique?
Item #2, i don't see a datetime column to capture THAT value in your datatable yet. TimePeriodValidity sounds more to me like something that should be calculated, and not actually stored in the record.
Item #3, Is This the Gift Vouchers Table, or a record of transactions? If you have to validate gift vouchers, shouldn't there be another table with all the gift vouchers in it? do you have the gift voucher number in This table so you can compare it to the other?
If this is the Gift Vocuehrs Tabe, why nickname it with GV_*?
why not the full name like GiftVouchers_OrdersReceived?
Item #4, how will you do this validation? in a client side application or via TSQL? have you fleshed out how you plan to do that yet? This item mentions Sales price, but your current table does not have any decimal/money data types to hold the values yet.
Other table basics: generally, every table should have a primary key
when possible, it's a good idea to use default values and check constraints to force data to be in expected ranges(ie you would never allow a negative gift voucher value, right?) if you have \Y/N flags, check constraints can enforce them
If you have things like status, instead of freetext, you generally want to create a table with the allowed values, and use a foreign key to enforce only specific allowed values.
Lowell
March 20, 2013 at 5:46 am
Lowell (3/20/2013)
kapil_kk (3/20/2013)
Hi,I have a scenario according I have to create a table struture. As I have never designed any table structure based on a scenario of SRS. Plz guide in this scenario
Application will receive the voucher order from the printing agency.
1.The user will enter the transaction ID of the order.
2.User will enter the receipt date as current date.
3.User will verify each gift voucher with gift vouchers records in the database.
4.On scanning the Gvs, application will validate
Voucher ID
Voucher denomination
Voucher sales price
Time period validity
First Voucher ID
Last Voucher ID
5.In case of the discrepancy in the count of the vouchers, application to prompt with error
6.In case of short receiving, the transaction ID will be open to receive the remaining vouchers for the period of 1 month from order raised date.
7.In case of excess, the additional vouchers will be sent back to the agency operationally
8.All open transaction IDs should be closed automatically after 1 month
9.Ho to receive the entire order & raise the discrepancy to the agency with the help of log file.
10.Voucher once in warded, should get active in the database as to sell to the customers.
11.Voucher once in warded, without any discrepancy, the application to complete post goods received and save inventory of the vouchers.
According to my understanding I have created this structure... Is it ok or anything else is required?
Create table GV_ReceiveHO
(
VoucherNo varchar(20),
VoucherDenomination int,
FirstVoucherID varchar(20),
Last VoucherId vaarchar(20),
TimePeriodvalidity datetime,
VoucherStatus varchar(20)
)
going by the problem's items, here's what I see:
Item #1, what if two different users enter the exact same voucher number? don't you want that to be unique?
Item #2, i don't see a datetime column to capture THAT value in your datatable yet. TimePeriodValidity sounds more to me like something that should be calculated, and not actually stored in the record.
Item #3, Is This the Gift Vouchers Table, or a record of transactions? If you have to validate gift vouchers, shouldn't there be another table with all the gift vouchers in it? do you have the gift voucher number in This table so you can compare it to the other?
If this is the Gift Vocuehrs Tabe, why nickname it with GV_*?
why not the full name like GiftVouchers_OrdersReceived?
Item #4, how will you do this validation? in a client side application or via TSQL? have you fleshed out how you plan to do that yet? This item mentions Sales price, but your current table does not have any decimal/money data types to hold the values yet.
Other table basics: generally, every table should have a primary key
when possible, it's a good idea to use default values and check constraints to force data to be in expected ranges(ie you would never allow a negative gift voucher value, right?) if you have \Y/N flags, check constraints can enforce them
If you have things like status, instead of freetext, you generally want to create a table with the allowed values, and use a foreign key to enforce only specific allowed values.
for #3 i have a gift voucher number from which I will validate the data.
#4 - Let me tell you the scenario for this- We will create vouchers and store in the giftvoucher table and this gift voucher will be sent to the printing agency to print those vouchers. After receiving those vochers from printing agency I have to validate those vouchers from gift voucher table and if they matched then I have to stored that data in the GiftVoucher_OrderReceived table .
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply