March 6, 2008 at 5:55 pm
I’ve finally gotten my tables with their constraints, relationships, insert, update and delete sprocs completed. Each sproc is working and has Try/Catch, Trans/Commit/Rollback, Raiserror and Set NoCount On. Where necessary, I have included Upper. Are there others that I definitely should use?
I’ve tested quite a bit, but am sure that something will pop up that I did not think of, such as another needed table or constraint, of which I will fix, but now I’m wondering: What next?
My plan is to create a front end with ASP.NET 2.0 and VB, but should I do more with SQL first? I do not want to do something in SQL that could/should be done in ASP, and vice versa, but I don’t know enough about each at this point in time to really decide which is best. Though I do know that I want to have my sprocs in SQL and call them with a line of code from ASP/VB.
For the following, if someone(s) would provide me with a bit of guidance, it would sure be appreciated. I know some of these are pretty newbie like stuff.
Should I create my views now using SQL?
Worry about security coding now?
If I ever get this to production, I would like to use hand held devices for some of the user input. I don’t think I need to worry too much about this right now. Correct?
On paper design my input forms, pull out the ASP.NET 2.0 book and begin reading or begin learning Reporting Services now?
None of the above?
I know that some of the above will happen together, but I am need of some direction.
As always, thanks.
Bill
March 6, 2008 at 10:00 pm
My 2 cents:
Is your database normalized? Is OLTP?
Create database diagram; data type/length should be consistent cross all the related tables;
Indexes, do not forget cluster index;
User access at different levels. Who should have what kind of access; etc.
Using stored procedure is a good practice as the execution plan is cached. The business logics (stored procedures) are within the database. No matter you use ASP.net or C# or VB, they are just application front end/client side programming, no (much) change if business logic changes.
March 7, 2008 at 7:13 am
From what you described, I'd move on to coding now. As you code, when you get portions of the app working, run Profiler while you test the app so that you capture the calls to the database. From there you can identify if there are procedures that are likely to cause you problems when app goes live (frequently called procs, long running procs, procs that return lots of data). Tune these as you find them.
That's my 1.5 cents.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 8, 2008 at 10:03 am
Is the DB Normalized? I believe so. I’ve set up tables for Customer, Jobs and Clients (the workers) basic info along with new tables with related info, such as Job Steps, client wages, vacation, etc. I have a number of lookup tables for things such as locations, work status, etc.
OLTP? This is a payroll processing program, so I believe it is OLTP.
DB Diagram? Yes. It has helped immensely with setting up and understanding relationships.
User Access? I need to sit down with a pencil, paper and SQL book to work on this part.
Run Profiler? I am using the Express editions of SQL 2005, Visual Web Developer and VB. I do not believe that they have Profiler. A good substitute?
As to clustering, indexes, etc., below is some info on the major tables and their columns. All PK_IDs are assigned by SQL. During data entry of new jobs, steps, timesheets, etc., data entry user enters (when required) the CustomerNumber, JobNumber, StepNumber, LocationNumber, ClientNumber, SSN, BatchNumber and WorkDate.
You’ll see that I have clusters on the data entered by the data entry person, except where a PK_ID is used by the sproc, then the PK_ID is included in the cluster.
Customers Table DataType Clustered KeyType
Customer # varcharYIX/U
Cust_PK_IDintNPK/U
------------
JobInfo Table
Cust_PK_IDintYIX/U
Job#varcharYIX/U
Location#varcharYIX/U
The above three together make up a key.
Job#_PK_IDintNPK/U
During job setup, update, delete and lookup, user enters a Customer# (sproc finds the Cust_PK_ID), Job# and Location#. SQL assigns or finds the correct Job#_PK_ID. Searches/setup will be done on Customer#/Job#/Location# combination entered by the user.
-------------
JobSteps Table
Job#_PK_IDintYIX/U
StepNumbervarcharYIX/U
Location#varcharYIX/U
The above three together make up a key.
Step#_PK_IDintNPK/U
During job-step setup, update, delete, lookup and timesheet entry, user enters a Customer# , Job# and Location# (SQL finds the appropriate PK_IDs). SQL assigns or finds the correct Step#_PK_ID.
-----------------
ClientInfo table (a client is a worker)
Client_PK_IDintNPK/U
ClientNumbervarcharYIX/U
SSNvarcharNU
Users will do data entry and searches using the ClientNumber. Very few searches are done with the SSN at this point in time. SQL assigns or finds the Client_PK_ID for other tables such as vacation, wage rates, timesheet entry, etc.
------------
ClientVacation (and other related tables)
Client_PK_IDintYPK/U
SQL searches on the Client_PK_ID based upon the value of the ClientNumber.
-----------------------
ClientTimeStudy table
Client_PK_IDintNU
Step#_PK_IDintNU
The above two columns make up the key. I’m thinking that they s/b clusterd.
TimeStudy_PK_ID intNIX/U
TimeStudy_PK_ID intYPK/U
I believe that I should not have two different key indexes setup for one column. Thoughts?
------------
TimeSheet table (this is where it all comes together)
Batch#varchar NU
Location#varchar NU
WorkDatesmalldatetime NU
Client_PK_IDint NU
Step_PK_IDint NU
The above make up one key. I’m thinking that it s/b clustered.
TimeSheet_PK_IDintNU/IX
TimeSheet_PK_IDintYU/PK
Once again, I believe that I should not have two different key indexes setup for one column. Thoughts?
As always, any help/input/suggestions are greatly appreciated.
Thanks,
Bill
March 8, 2008 at 10:50 am
"User Access? I need to sit down with a pencil, paper and SQL book to work on this part."
--> Need business owner/system analyst to define the user access. Payroll system is the most restrictive area, right?
My 1 cent.
March 8, 2008 at 4:56 pm
In this situation, I am the system designer/administrator/payroll person. so it falls to me.
Any thoughts on the other items?
Thanks,
Bill
March 9, 2008 at 10:36 am
Hi,
few inputs, now you need to think about what are the data access patterns. prepare good documention around database. then move on to the front end code and test the application.
Thanks -- vj
March 10, 2008 at 6:21 am
Application/database design and development is an iterative process. Don't expect to get each piece 100% complete or 100% correct on the first pass...it is generally not realistic. As Grant said, things will surface as you code and test and you can make changes as you go to handle those issues.
I find that my database modeling can change some as I'm writing the front-end code (additional columns, new tables, changing columns to support a data relationship I didn't catch the first time, de-normalization, users changing their mind on the design), so therefore I don't spend too much time on indexing until the database model has stabilized.
I don't write views until I have a requirement for them (reporting, abstraction, etc.) because I wouldn't know what to put in them until there is a requirement.
Hope this helps! Have fun with it!
If it was easy, everybody would be doing it!;)
March 10, 2008 at 2:46 pm
Grant/VJ/Sam,
Thanks.
Bill
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply