jknj72
asked on
SQL Server DB architecture
I have to create a db and I have a question. The db will be for an employee to enter the work items they worked on for a given day. The employee will also have to enter any kind of expenses for that day. I have created a table for WorkItems(holds all the info for the work item) and a table for TravelItems(holds all the info for the travel item). I would like to have another table act as a main table for entering the initial data for the "Item". So I was thinking about creating an Item table and whether or not the entry was for a Workitem it would have an TypeID of lets say 1 and if its a TravelItem then have a 2 in the Item table under a field "TypeID". My conflict is that I want to have a foreign key in the Item table that would be the primary in the WorkItem table and that same TypeID be a foreign key in the TravelItem table?
SO I guess my question is how would anyone of you go about designing this type of table that could somehow hold the foreign keys for both of these tables. Im not stuck on this design but want some opinions before I go any further. I have a pic attached and have the
ItemsID in the ItemTracker table related to the primary of the WorkItems and the primary of the TravelItems table. THis is where Im needeing help
SO I guess my question is how would anyone of you go about designing this type of table that could somehow hold the foreign keys for both of these tables. Im not stuck on this design but want some opinions before I go any further. I have a pic attached and have the
ItemsID in the ItemTracker table related to the primary of the WorkItems and the primary of the TravelItems table. THis is where Im needeing help
Brian Crowe
Can you expound a bit on what WorkItem and TravelItem represent? I interpret WorkItem sort of like a timekeeping system where I said I worked on Project A for 2 hours and TravelItem as an expense report item. If that is the case then I wouldn't try and store them in the same object as you will need a relation between TravelItem and WorkItem for accounting purposes.
ASKER
Well you are correct. Work Items are what they have worked on and its on a sheet and has fields about what they have done and the time it took etc...
Travel items are similar but they are about their travel routes and tolls, etc...
I want to have both in the same db. I have the first thought of how to create the db in the pic attached. I wanted to be able to add a line item with a typeID. The typeID will tell me if its a work item or a travel item. I figured that a main table would be the way to go and then have the type tell me what kind of record it would be.
Check out the diagram I attached and let me know what you think...I would like to keep this in something similar to what I have but let me know what you think....I "had" a foreign key relation to the WorkItem primary and the TravelItem primary to the TypeID to the ItemTracker table but I took it off for now
Thanks
relations.jpg
Travel items are similar but they are about their travel routes and tolls, etc...
I want to have both in the same db. I have the first thought of how to create the db in the pic attached. I wanted to be able to add a line item with a typeID. The typeID will tell me if its a work item or a travel item. I figured that a main table would be the way to go and then have the type tell me what kind of record it would be.
Check out the diagram I attached and let me know what you think...I would like to keep this in something similar to what I have but let me know what you think....I "had" a foreign key relation to the WorkItem primary and the TravelItem primary to the TypeID to the ItemTracker table but I took it off for now
Thanks
relations.jpg
ASKER
Let me go back a little. I had a field in ItemTracker that was ItemsID after the primary ItemID that was the foreign key for both the WorkItems and TravelItems table primaries. I must have taken that out.
I suggest going back a little more. Do a logical design first, i.e., forget about "tables" for now and just look at the actual data entities (kind of like "tables") and attributes ("columns") and their relationships. You need to iron all those relationships out before you try to convert it into physical structures. Skipping the logical design phase is tempting, but it can lead to vastly more re-work later.
ASKER
ok well you have seen my tables...I have an employee and that employee gets work assigned to them. and they must keep track of that work. The employee also has to keep track of the travel associated with that work. The tables reflect the sheets that they have to fill out at the end of the week. So the entities start at the employee. The employee has work items and travel items associated with them. As far as I can see the main entities are the employee, work item and travel items.
I can get all these entities defined but I need a way of tracking the employee work items and travel items figured out. I want to figure out how the relations can be mapped out from the employee to the travel item and the employee to the work item but I want to do it correctly so rather than have a foreign key in the "main" table point to both the travel and work item I want to know how I can map both of them correctly.
I can get all these entities defined but I need a way of tracking the employee work items and travel items figured out. I want to figure out how the relations can be mapped out from the employee to the travel item and the employee to the work item but I want to do it correctly so rather than have a foreign key in the "main" table point to both the travel and work item I want to know how I can map both of them correctly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok I am going with the separate tables as you said. This was my initial design so I will be giving you the points. Thanks for your help..
ASKER
thanks for the help