Link to home
Start Free TrialLog in
Avatar of paulj1999

asked on

Creating Multiple Views


     I've asked this question before but I think I've come to an epiphany with the answer.

I have the following DB schema (4th normal form):


     I would like to create a view on the following tables:

table project:
     columns:   project_id, name, creation_date, creation_id, subject

table project_assignments
     columns:   project_assignment_id, project_id, position_id, user_id

table project_positions
     columns:   position_id, definition

table users
     columns:   user_id, username

Now, I want to denormalize and have
project_id, name, creation_date, creation_id, subject, position 1, user 1, position 2, user 2, etc.

I was thinking of doing a view dealing w/ positions and then building the view from there.

For example:
position1_view = project_id, position 1, user 1   where position 1 is unique
from there, build a total positions view:
total_postions_view = project_id, position1_view, position2_view, etc

Then my last view returns project_id, name, creation_date, creation_id, subject, total_positions_view

To me this seems logical, but is this practical?  is something like this something worth considering?  The only downside is if I add a new position I would have to change the total_positions_view.    Is this something that's done normally?  Basically the client wants to have rows of records like the above, not broken up by project then have a sub category of positions.

Avatar of mankowitz
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial