Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
deco10
Starting Member
28 Posts |
 Posted - 2010-11-29 : 00:04:35
|
I have an existing database that I think requires some redesign before the application goes to production.The app is to do wtih schedule and company meetings. I'll give you the cliff notes version.issues (id, start_date, name, overseer - int, area_manager - int) (overseer and area_manager are a person id)people (id, name, birthdate, position)issue_people (issue_id, person_id, issue_role - int) roles (id, role_name) ("note taker", "assistant", "advisor", etc etc) meeting (id, issue_id, start_date_time)meeting_people(meeting_id, person_id) (there could be several of each role involved in a meeting, but not all the people are involved in every meeting)a meeting has many people and 1 issue. An issue has many people and 1/many meetingsSo the people are listed in the issue_people table and then again in the meeting_people table. It seems a redundant. a meeting belongs to an issue. One of the things that will be done regularly is querying the database to see if there are meetings that don't have at least one person in a certain role (for example I would need to find all the meetings in which a "note taker" has not been assigned. Any advice?Thanks!  |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-11-29 : 04:05:44
|
| How does people get assigned to a role in a meeting? Is people.position the same as roles.role_name (will it say i.e. "note taker" in the people.position column? And do all meetings need to have all roles assigned? Are all the roles the same for all meetings?- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-29 : 04:34:37
|
| The issue is applicable to all meetings about that issue.The issue people table reflects that the role is for al the meetings for that issue.The metting people table reflects the relationship just for a single meeting about the issue.It's not redundant but may not be correct. I would be surprised if the note taker role was filled by the same person for all meetings - could be the person that is responsible for notes being taken (i.e. could delegate for a meeting but is responsible for ensuring that minutes are distributed) or maybe there are many of them? i would expect this to have a start and end date - reflecting the responsibilitu changing - what happens if the person responsible for the issue (manager?) leaves and is replaced?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
deco10
Starting Member
28 Posts |
Posted - 2010-11-29 : 09:32:15
|
| There is a person in the company that sets up the issues and subsequent meetings, assigning people as necessary. Keep in mind that this is a mocked up example and not exact scenario I'm working with. There could be one manager for one meeting, another for a second meeting and then the first manager for the third meeting. And so on.It seems like I should be able to to find the people involve by collecting them from the meetings that pertain to an issue. |
|
|
|
|
|
|
|
|
design correctness - help