jknj72
asked on
Database architecture
I have been assigned to build an app. Before building the app I have to build the DB. The app is for Inspectors/Auditors and it will track their work items and their expense items for their work week.
The employee(Auditor/Inspector
So I have a copy of an Auditors work item list and expense item list that they filled out. I have to create the DB based off of this list.I pretty much was told how the DB should be created and I have had some issues with how the manager wants me to create the DB so Id like to get some opinions and this forum is by far the most knowledgeable that I've used on this site so I would love to hear some advice from some of you about what you think I should do as far as designing this DB.
I thought about how the data will be coming to me. Their is a table that will be filled with the items the employee will have to work n through the week. With that, I thought that I would have 3 main entities. The work items, the expense items and the employee. The work items I was thinking on displaying in the left side of the screen by week(In a treeview). When they click on a folder of work items they have worked on I will have all their work items in a grid and they can number them and order them the way they worked them during the week. The employee will go thru their items, when they worked on them and order them by job and by day..
I attached a pic of list of work items they will need to enter into the new system at the end of the week.
So like I said attached is a completed list of work items that the employee has worked on during the week and what they did, day by day. I also have attached an expense items pic of the tolls, gas, mileage, etc.... for the week
I just would love some advice on how I can create the database to store the data so that they can enter their items at the end of the week,
Any help would be greatly appreciated
WorkItems.png
ExpenseItems.png
The employee(Auditor/Inspector
So I have a copy of an Auditors work item list and expense item list that they filled out. I have to create the DB based off of this list.I pretty much was told how the DB should be created and I have had some issues with how the manager wants me to create the DB so Id like to get some opinions and this forum is by far the most knowledgeable that I've used on this site so I would love to hear some advice from some of you about what you think I should do as far as designing this DB.
I thought about how the data will be coming to me. Their is a table that will be filled with the items the employee will have to work n through the week. With that, I thought that I would have 3 main entities. The work items, the expense items and the employee. The work items I was thinking on displaying in the left side of the screen by week(In a treeview). When they click on a folder of work items they have worked on I will have all their work items in a grid and they can number them and order them the way they worked them during the week. The employee will go thru their items, when they worked on them and order them by job and by day..
I attached a pic of list of work items they will need to enter into the new system at the end of the week.
So like I said attached is a completed list of work items that the employee has worked on during the week and what they did, day by day. I also have attached an expense items pic of the tolls, gas, mileage, etc.... for the week
I just would love some advice on how I can create the database to store the data so that they can enter their items at the end of the week,
Any help would be greatly appreciated
WorkItems.png
ExpenseItems.png
Jim Horn
Just to set expectations, it appears you are asking us to perform design analysis on a set of requirements for an entire project, which is greater in scope then a typical EE question. Aside from any advice you may receive in this quesion, please consider using Gigs to hire this out.
ASKER
Well I definitely dont want to have anyone do the project. I am asking for suggestions on my design of the DB. I have one created already but Im not happy with it as of right now. I can do it the way it is but I thought maybe some advice on how someone else would do it. Not a detailed explanation but thought I could get a quick explanation on how someone else would go about doing this. If others feel the same then I will delete the question
>> just would love some advice on how I can create the database to store the data so that they can enter their items
The advice is easy:
The trick then is to make sure the data you capture can be turned back into information when you need it.
I've seen a few databases and applications what were great at capturing data. When it came time to query it all back out, it couldn't be... Simple things like capturing an event and a date/time but not being able to join them back together to know what event actually occurred at what date/time.
The advice is easy:
- Don't let the UI design drive the database design.
- Follow the Rules of Normalization
The trick then is to make sure the data you capture can be turned back into information when you need it.
I've seen a few databases and applications what were great at capturing data. When it came time to query it all back out, it couldn't be... Simple things like capturing an event and a date/time but not being able to join them back together to know what event actually occurred at what date/time.
As far as the overall design, I'm not sure three main entities would be enough.
Some real quick observations:
new entity: expense_type
new entity: customer_location
No need for day and date. Store the date and you can extract the day.
Some real quick observations:
new entity: expense_type
new entity: customer_location
No need for day and date. Store the date and you can extract the day.
ASKER
Funny you say that. I had a design in my head and it was front end driven. Ill figure it out...Thanks
While you are thinking things out, go ahead and leave this open for a while.
I'll do what I can to help. Just ask...
I'll do what I can to help. Just ask...
ASKER
I do have the expenseType table already. The customer location gets downloaded when its assigned to the user so I am saving that field but really just saving that as a column in either the WorkItems table or the ExpenseItems table
>>customer location gets downloaded when its assigned
As long as you can maintain data integrity. You probably don't want end users typing in the location. You will end up with Howell in some records and Howel in others.
You can look back through the questions asked on the site and find TONs of them where people were trying to write SQL against dirty data that some application let into the database.
As long as you can maintain data integrity. You probably don't want end users typing in the location. You will end up with Howell in some records and Howel in others.
You can look back through the questions asked on the site and find TONs of them where people were trying to write SQL against dirty data that some application let into the database.
ASKER
ok cool thanks...glad I saw your last post I was just closing the question.
To explain it a little further. I have a WorkWeekTracker table that I was going to use as a tracking table by week. Each week there will be a new row entered into the table that will increment the IDs so I can keep these tables relational.
This table is designed like this
WorkWeekTracker
[WorkWeekTrackerID] [int]
[WWT_WorkItemID] [int] ,
[WWT_ExpenseItemID] [int] ,
[WWT_ImageID] [int] ,
[WWT_WeekID] [int] ,
[WWT_UserID] [int] ,
[WWT_WI_ApprovalDate] [datetime] ,
[WWT_EI_ApprovalDate] [datetime] ,
[WWT_EI_Signature] [varchar](50)
For Work Items:
I am using a WorkItem table as a cross reference type of table to join WorkWeekTracker and another table WorkItems.
So WorkItem will hold data like this
WorkItem
[WorkItemID] [int] , (Primary Key)
[WI_WorkItemID] [int] , (Joined to WorkWeekTracker)
[WI_WorkItemsID] [int] (Joined to WorkItems)
And it would have data like this
WorkItemID WI_WorkItemID WI_WorkItemsID
1 1 1
4 1 2
5 1 3
6 1 4
7 1 5
8 1 6
9 1 7
10 1 8
11 1 9
12 1 10
13 1 11
14 1 12
15 1 13
16 1 14
17 1 15
18 1 16
19 1 17
To explain it a little further. I have a WorkWeekTracker table that I was going to use as a tracking table by week. Each week there will be a new row entered into the table that will increment the IDs so I can keep these tables relational.
This table is designed like this
WorkWeekTracker
[WorkWeekTrackerID] [int]
[WWT_WorkItemID] [int] ,
[WWT_ExpenseItemID] [int] ,
[WWT_ImageID] [int] ,
[WWT_WeekID] [int] ,
[WWT_UserID] [int] ,
[WWT_WI_ApprovalDate] [datetime] ,
[WWT_EI_ApprovalDate] [datetime] ,
[WWT_EI_Signature] [varchar](50)
For Work Items:
I am using a WorkItem table as a cross reference type of table to join WorkWeekTracker and another table WorkItems.
So WorkItem will hold data like this
WorkItem
[WorkItemID] [int] , (Primary Key)
[WI_WorkItemID] [int] , (Joined to WorkWeekTracker)
[WI_WorkItemsID] [int] (Joined to WorkItems)
And it would have data like this
WorkItemID WI_WorkItemID WI_WorkItemsID
1 1 1
4 1 2
5 1 3
6 1 4
7 1 5
8 1 6
9 1 7
10 1 8
11 1 9
12 1 10
13 1 11
14 1 12
15 1 13
16 1 14
17 1 15
18 1 16
19 1 17
ASKER
I think if I can get the Work Items portion of the DB figured out I can apply it to the other items I have, like Expense and Image items.
ASKER
And I agree with the limiting of the user to enter data. I will see if I can find the table where they pull locations and populate a dropdown or something for them to select the location
>>will increment the IDs
That is a red flag that should tell you that you may not be normalized. I'm not a fan of surrogate keys but they have their place. You just need to make sure they are justified when you use them.
Many times they are a 'bandage' to stop the bleeding of a bad design.
>>Each week there will be a new row entered
I don't like this. Someone will be setting up the work schedule. Whenever this is done, you have your record that drives everything else.
Think of it like an Order. You don't pre-create orders and fill in the line items. Someone initiates an order and data is generated at that time. Now an Order might generate a key value and it is incremented. Remember, it needs to make sense when you use them...
Also keep in mind that 'gapless' numbers are really not worth the effort to try an generate. So don't try... Come up with a key generating mechanism that doesn't need 100% gapless numbering.
That is a red flag that should tell you that you may not be normalized. I'm not a fan of surrogate keys but they have their place. You just need to make sure they are justified when you use them.
Many times they are a 'bandage' to stop the bleeding of a bad design.
>>Each week there will be a new row entered
I don't like this. Someone will be setting up the work schedule. Whenever this is done, you have your record that drives everything else.
Think of it like an Order. You don't pre-create orders and fill in the line items. Someone initiates an order and data is generated at that time. Now an Order might generate a key value and it is incremented. Remember, it needs to make sense when you use them...
Also keep in mind that 'gapless' numbers are really not worth the effort to try an generate. So don't try... Come up with a key generating mechanism that doesn't need 100% gapless numbering.
ASKER
I dont need gapless numbering. Heres the thing...The data that comes across from the system and the user gets a copy of what they need to do in another system. At the end of the week, I am gonna have a copy of that data for the user to put in order of items accomplished as well as dates and times of when the work was done, etc...(along with any type of expenses). So at the end of the week is when the user would use this system. So when I have this data(work items only), I would need it stored for the user to manipulate when they are ready. This is one of the reasons I found this DB not your typical DB because we receive the work items before the user would be entering it in our system. The user would essentially be updating that data ...
>>I found this DB not your typical DB because
Sounds like a typical database to me. Very rarely is a system the originator and final consumer of data. Just about every system I've ever worked on received data from somewhere and sent data somewhere else.
>>The data that comes across from the system
So when you said "Each week there will be a new row entered", you aren't generating the new row and you are consuming it as a feed from a parent system?
That is OK.
I just didn't like the idea of you or your code having to remember: OH, it's time to generate a new set of weekly records or nothing works.
There is still something that I don't like about your WorkWeekTracker table but I don't know enough about the whole design to comment on it.
I'm not seeing the need for WorkItemID and _ExpenseItemID in that table.
Sounds like a typical database to me. Very rarely is a system the originator and final consumer of data. Just about every system I've ever worked on received data from somewhere and sent data somewhere else.
>>The data that comes across from the system
So when you said "Each week there will be a new row entered", you aren't generating the new row and you are consuming it as a feed from a parent system?
That is OK.
I just didn't like the idea of you or your code having to remember: OH, it's time to generate a new set of weekly records or nothing works.
There is still something that I don't like about your WorkWeekTracker table but I don't know enough about the whole design to comment on it.
I'm not seeing the need for WorkItemID and _ExpenseItemID in that table.
ASKER
yes I agree...Its one of the reasons I posted this question. I am having trouble wrapping my head around this design. Let me take a step back and maybe we can come up with something.
Here are the steps I need to accomplish. I wanna stay with WorkItems because this is my main issue and this has data before the user uses this system.
Steps:
1. Data comes across from other system. The user gets this data and I will have it as well.. I am storing this data in a WorkItem_Queue table. These are the records I attached in a pic called WorkItems. Pic is after the user records the data for the week.
2. In the beginning of the week after I receive the data, I was going to populate the WorkWeekTracker table with a row that will identify these records with the new IDs(this is where I was going to generate a single ID to identify these records) so we can track when the user puts there work in for the week. (Not crazy about this idea either).
3. When the user is ready they will order the items and when they worked on them which is when I will be inserting into the WorkItem and Items table to store the data they entered.
Maybe eliminating the Tracker table would make sense. Maybe keep the data in the Queue table until the work is being entered. Then by Inserting the data from the Queue table into the WorkItem and Items table? What do you think?
Here are the steps I need to accomplish. I wanna stay with WorkItems because this is my main issue and this has data before the user uses this system.
Steps:
1. Data comes across from other system. The user gets this data and I will have it as well.. I am storing this data in a WorkItem_Queue table. These are the records I attached in a pic called WorkItems. Pic is after the user records the data for the week.
2. In the beginning of the week after I receive the data, I was going to populate the WorkWeekTracker table with a row that will identify these records with the new IDs(this is where I was going to generate a single ID to identify these records) so we can track when the user puts there work in for the week. (Not crazy about this idea either).
3. When the user is ready they will order the items and when they worked on them which is when I will be inserting into the WorkItem and Items table to store the data they entered.
Maybe eliminating the Tracker table would make sense. Maybe keep the data in the Queue table until the work is being entered. Then by Inserting the data from the Queue table into the WorkItem and Items table? What do you think?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No I think your right since you brought that to my attention. I was kind of in the state that I need to do something with the data when I get it to get it ready for when the user is ready to enter their workitems. I am going to try and go this route and I think it will make more sense and simplifies the whole thing. Im gonna give you the points and move forward. Thanks as always for your help
ASKER
thanks again