February 2, 2011 at 2:50 pm
Hi,
I am designing a database to track expenses and invoices for a company and need to add in the ability for the company to have different levels of online approval of the items, depending on the expense type and value.
Currently I plan to have a 'User' table that includes many fields but particularly the following fields:
ID
User Name
User Type
Password
As this is a question about what structure I will have I have not got anything concrete to post in code. However, the plan is to have a table for each expense item details and a table with the invoice details as listed below:
Expense Table
ID
ExpenseTypeID
Expense Description
Expense Amount
InvoiceID
SupplierID
Created By
Created Date
Invoice Table
ID
Invoice Description
Invoice Amount
Expense Type Table
ID
ExpenseTypeDescription
ApprovalRequiredAmount
The idea is for the user to enter an expense or invoice. The database will then recognise what type of approval is required and alert the relevant approver. Once the person has approved the item, it may require someone at a higher level to further approve the item. This is where I am unsure how to structure the database. I could have an ApprovedByID and ApprovedDate in the Expense Table or Invoice Table, but that would only allow one approver. I may need 2 or 3 approvals if the item/invoice is of a large value.
So if anyone has any ideas on this it would be much appreciated. I will be using Microsoft Access adp as my front end.
Thanks
February 2, 2011 at 5:07 pm
User Table
ID --Why such a generic term is it the user's Id or?
User Name
User Type -- this could be a limited standardized item - kept in a lookup table, and referenced via a foreign key. Again minimizing input error
Password -- why a password ?.
1, SQL server, can if you want, require a user to login using a password. Check security features in Books On Line.
If you still believe you need it:
1. How do you plan to protect this Password so others can not see it?
Expense Table
ID -- Is this to identify the user who entered values in this table?
If so why is it not the Id from the "User Table .. review the concept of foreign keys to insure at least some data integrity for entries in this table
ExpenseTypeID
Expense Description -- If these will be "standardized" for example
'Air Fare" / Motel / Meals / car rental and so on and so forth .. put those in a "look up" table and link to that table.
This could save a lot of future grief .. for example a misspelled word
such as "Air Faer" or some other key board goof. By reducing user input to selecting a predefined value you could save yourself a lot of future grief.
Expense Amount
InvoiceID
SupplierID
Created By -- Again this should be the user ID not a typed in name.
Created Date -- If this is meant to be the date of the entry into the table, set a default value of GETDATE(). Otherwise there is a potential of numerous input errors.
Spend some time studying what is termed Normalization... and then come back with your design.
From a posting by john.arnott
http://en.wikipedia.org/wiki/Database_normalization
After that, use your search engine of choice (Google, Yahoo....) to find info on E.F. Codd, "Normal Form", "Data Modeling", and other terms in the Wiki article.
February 2, 2011 at 5:49 pm
Hi,
Thanks for your reply and suggestions. To answer your questions, yes the database will be normalised.
The CreatedBy will be labelled CreatedByID and will refer to the ID in the user table (mistyped in original post...sorry).
The ID in the user table is the User's ID, and I will relabel as UserID so as to be clear.
The ExpenseTypeID refers to the ID in the ExpenseType table. This was done for normalisation purposes. I will relabel the ID field in the Expense Type table to ExpenseTypeID so it is clear.
The CreatedDate field will use the GetDate() function.
The InvoiceID refers to the ID field in the Invoice Table (I will relabel also).
Basically I have no problem with normalisation and will definitely be using it extensively. I am not as concerned about that part of the design for this post.
What I am really trying to sort out is the data structure for an approvals process/functionality within the front end. This would utilise the UserID in the Users table so all user information is normalised.
Again, thanks for your response and suggestions. Any further input about the apporvals part would be very much appreciated.
Thanks
February 2, 2011 at 6:26 pm
This is where I am unsure how to structure the database. I could have an ApprovedByID and ApprovedDate in the Expense Table or Invoice Table, but that would only allow one approver. I may need 2 or 3 approvals if the item/invoice is of a large value.
If I understand your needs properly I would suggest you examine the (new in 2008) Hierarchyid Data Type, it seems to be what you require.
I have listed a few links that might assist you in making that decision.
http://technet.microsoft.com/en-us/magazine/2008.04.datatypes.aspx
http://technet.microsoft.com/en-us/library/bb677290.aspx
http://blogs.msdn.com/b/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx
Tutorial: Using the hierarchyid Data Type
http://technet.microsoft.com/en-us/library/bb677213.aspx
If the Hierarchyid Data Type is not what you think you need ,come on back and post again, or if you decide to follow that path and have problems, open a new forum posting and ask for assistance.
February 2, 2011 at 10:20 pm
Hi Ron,
Thanks for the information on hierarchyid. It looks like something I could use. However I have come across a little bit of a problem. I am using Microsoft Access 2003 and will be upgrading soon to Access 2007 for the front end. When utilising hierarchyid in 2003 it does not recognise the data type. And it seems from browsing the internet that it is only Access 2010 that supports this new datatype. I am definitely not going to be able to upgrade to 2010 any time soon.
If there are any other suggestions on the best way to structure this kind of approvals system then that might be helpful also, as it doesn't look like I am going to be able to use hierarchyid with my current setup.
Thanks again
February 3, 2011 at 7:10 am
After a search of previous forum posts (under the title "Bills of Materials") I ran across this reference, posted by Jeff Moden at:
http://www.sqlservercentral.com/Forums/Topic414386-169-1.aspx
Doing what Jeff recommended in the above I came up with this link:
http://technet.microsoft.com/en-us/library/aa172799(SQL.80).aspx
Now it is from BOL for SQL 2000 and It contains a great explanation and a tremendous about of T-SQL code, and may be suitable for your project. Give it try and if you have further problems ... well repost to the forum or a new forum.
February 7, 2011 at 8:30 pm
Hi Ron,
Thanks for your reply and links. I should be able to use something like this and appreciate your help.
Regards
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply