Database Expense Approvals Structure and Functionality

  • 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

  • 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.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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

  • 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.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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

  • 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.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • 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