November 4, 2008 at 7:42 am
Hi folks, hope all is well.
I'm perplexed by a database I'm working on. I'll explain the process...
It's for a company who report on organisations. There is a Client (who is reported on), there are 4 projects the client is reported on. Each project has 7 milestones and each milestone has 6 fields including dates, comments etc.
Just to note: each milestones for each project has a different name.
This would be easy if it was 2 milestones with 2 fields each. I'd put the 4 milestone fields on the same table also with 2 milestone ID columns that would reference a milestoneName lookup table.
As there is 6 fields for each which makes 42 fields (48 inc Milestone ID's) then I'm obviously reluctant to do it this way. It would be easier since the frontend is an infopath form and I could just put all the fields on the form and automatically populate the milestone fields with a "project" dropdownlist.
If anyone can help me design this i'd be very grateful.
Thanks.
November 4, 2008 at 7:59 am
I'm a little confused here so bear with me. Based on your brief description I counted 3 potential schemas, Client, Project and Milestone. It sounds like a single Client can be associated with many projects. Similarly each Project can have many milestones.
So based on this it looks like you have a couple of one-many relationships.
The Client table would have ClientID, ClientName and all of the other fields that describe a client.
The Project table would have ProjectID, ProjectName, ClientID... Where ClientID is a foreign key relationship with Client.ClientID.
Finally, the MileStone table would have MileStoneID, MileStoneName, ProjectID, and all of the other fields that describe a MileStone. ProjectID would be related to Project.ProjectID.
Now where I get confused is how you came up with 42 fields for MileStone. You mentioned that MileStone has 6 fields (you may need to add the foreign key to Project to make this 7 fields).
I'm wondering if you're getting rows and fields mixed up?
November 5, 2008 at 4:30 am
Hi Karl, thanks for your response,
Heres what I did, The Client table is parent of Project and Project is parent of Milstone, now since there are only 7 milestones (and no more) this is where I was loosing my focus.
It's correct in normalisation terms for me to make one milestone field and since all milestones have 6 fields, Date etc, there would be 7 milestone field columns. Each new milestone is entered into a new row etc.
The reason I mentioned 42 fields, is that I was thinking about making a milestone it's own column along with all the dates (column1-Milestone1, column2-Milestone2 etc).
I'm developing an Infopath frontend and this would be easier to do it this way just cause I can layout all the fields in one form, the user picks a project from a drop down list and the milestone fields automatically populate with code, that kind of idea.
But if I make one milestone field which means 7 rows, instead of one, I'm not sure I'll be able to execute my idea on the Frontend Infopath form, like populating all 7 milestones with the project dropdown list.
I hope that makes sense? What do you think?
Thanks
November 5, 2008 at 5:25 am
Hi,
I see where you coming from now. From a relational database point of view it certainly makes sense to stick with a MileStone table and accept that you're going to have 7 rows. This method is much more flexible - if you ever have to add an eighth row or more that's easy, whereas adding a column and changing the code to support that won't be easy.
I know it can be pretty easy to assume things will never change but I've always started off with assumption that it's inevitable that things will change.
Now, obviously this seems to be in conflict with what you want to achieve on the front-end but it doesn't necessarily need to be the case. I can't visualize what you want on the front-end exactly but I think I get the idea.
When you select the Project from the dropdown list, can't you then execute a stored procedure (let's call it GetProjectMileStones) to which you pass the ProjectId as a parameter. Here's what it could look like:
create proc GetProjectMileStones
(@ProjectId int)
-- this procedure is called when someone selects a project from the project dropdown list
--and it retrieves all of the milestones for the supplied project.
as
set nocount on
select column_list from MileStone where ProjectId = @ProjectId
go
Then on your front-end all you need to do is loop through the resulting record set to get all 7 Milestones. The beauty of this is that if you ever add another Milestone, no code ever needs to change.
Like I said though, I can't exactly visualize what you want on the front-end so I might be barking up the wrong tree.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply