Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2016
    Posts
    8

    Am I overthinking this? (nested join tables)

    Click image for larger version. 

Name:	image-16-08-16-04-55.jpeg 
Views:	9 
Size:	894.1 KB 
ID:	17082
    Hello. I am working on a very simple Issues Tracking software (it's a forum with 'users access'). I have been watching normalisation tutorials and I have followed the logic of those. But it seems I have started to go deep into nesting in order to meet the logical side of the data. I am starting to think that I am overinterpreting the way this simple database should be designed.

    I will explain the web-app in short: The user, that belongs to a company, logs in and can see projects she/he is assigned to (ProjectAccess). Then they are able to raise an Issue within a Project. The Issue is pretty much like a forum thread, to which Users assigned to this Project have access to. Then each of these Users can leave a comment (until the Issue has been 'resolved' and 'closed').

    Now the problem area: I think the majority of my DB design is to sufficient standard to get me started with web-app. The main issue I have is with ROLES (the tables within dashed line on the attached image). The roles(like Techician, Mech. Engineer, Architect and so on) are assigned to Companies (subcontractors involved in Projects) -> Company_Roles gets populated. The Company then assigns the Roles given to them to the Users (that obviously belong to their company) -> UserRole table is populated. Meanwhile, the Project has got Roles 'raised' by the project owner (e.g. this Project needs Architect, Designer and Electrical Engineer) -> Project_Roles gets populated. And at the very end Project_Roles are being matched with User_Roles (e.g. Architect needed for Project (vacancy) is matched with an Architect assigned by the Company that received Architect Role).

    All I need is for someone to tell me if this is even close to correct. It's all based on self-learning and assumptions. Many thanks for all the input in advance.

    EDIT: Just rotated the image.
    Attached Thumbnails Attached Thumbnails image-16-08-16-04-55.jpeg  
    Last edited by vos; 08-17-16 at 09:20. Reason: Rotating Image

  2. #2
    Join Date
    May 2016
    Posts
    81
    Hi Vos,

    I propose you to implement your logical data model step by step and enrich the model as we are discussing


    Conceptual DM - Draft 1
    Click image for larger version. 

Name:	IssuesTrackingCDM.jpg 
Views:	5 
Size:	108.3 KB 
ID:	17116

    Logical DM - Draft 1
    Click image for larger version. 

Name:	IssuesTrackingLDM.jpg 
Views:	4 
Size:	117.0 KB 
ID:	17117




    Question:
    What is an event?
    Attached Thumbnails Attached Thumbnails IssuesTrackingCDM.jpg   IssuesTrackingLDM.jpg  
    Last edited by informer; 09-16-16 at 14:08.

  3. #3
    Join Date
    Aug 2016
    Posts
    8

    Thank you for responding

    First of all, thank you for responding to my question.

    I will have to educate myself better to advance on these schematics. I will analyse them in depth tomorrow.

    Could you elaborate on your question about 'event'? Did you mean all the events that may happen in the app or the 'event' table that is in your diagram? Or did you mean I should ask myself this question? (I feel stupid asking)

  4. #4
    Join Date
    May 2016
    Posts
    81
    Hi,
    No question is stupid☺ and if I ask you what are events it's just because on your scheme there is an event entity. Don't forget it's Just a proposal because I don't know exactly your functional rules
    Last edited by informer; 09-16-16 at 17:43.

  5. #5
    Join Date
    Aug 2016
    Posts
    8

    event

    OK, I see. The event on my chart refers to 'calendar' entries. This is unrelated with the 'forum' part. There will be a side bar informing of coming events, e.g. site visit, concept design issue deadline etc. It will serve just as information (maybe an email notification of upcoming events would be added at later stage). That's all it is, just an info for the users. Doesn't tie up with any logic of the app, just with Project - the events are project specific.

    Project is for example a building refurbishment. Issue is, when, for example, someone placed a plug socket behind a radiator on our plans/3d model. Anyone who noticed the problem starts a new Issue (which is effectively a problem-related forum thread), where users can discuss how to resolve the matter, can attach documents, screenshots, images etc. When they decide that moving a radiator solves it and the solution has been accepted, the Issue is resolved (and closed).

    The Users have to have access rights to Projects (which might be based on roles they are being assigned). The Roles are defined by British Standards.

  6. #6
    Join Date
    May 2016
    Posts
    81
    Hi,



    It's my turn to ask you question:

    Is a project used to categorize a threat?
    How is assigned a role to the users? Is a super User who assign it or is it the user?

    I propose you to add document entity connected to issue entity with a relation "attach" and cardinality (0,n) - (1,1)

  7. #7
    Join Date
    Aug 2016
    Posts
    8
    Yes, the Project is used to categorise a thread/threads (i.e. an Issue). The user will log in to a dashboard, where he/she can see all the Projects they 'belong' to. After clicking on a Project, they will see all the Issues list. After clicking on an Issue, the user sees the 'conversation' ( whole thread). Pretty much like in this forum.

    And yes, there is a super user ( 'Project Leader' let's say ), who can administer access and invite users. Some users may only be involved in a Project for short time, rather than its full lifespan.

  8. #8
    Join Date
    May 2016
    Posts
    81
    Hi
    Does a project leader have the same rights for all projects of his Company? It means that few users are declared project leader for all projects of his company
    Last edited by informer; 09-17-16 at 09:54.

  9. #9
    Join Date
    Aug 2016
    Posts
    8
    Quote Originally Posted by informer View Post
    Hi
    Does a project leader have the same rights for all projects of his Company? It means that few users are declared project leader for all projects of his company
    Hi.

    That should not be the case. A user becomes a 'Project Leader' per project basis. But yes, few users could have a project leader status on one Project (although this is not necessary, this would prevent unwanted issues in case the primary person is unavailable and there is a need of inviting new users).

    Also Project Leader sends invites to other companies users. I'll explain: Company A is the main contractor on Project X, but they need Company B and Company C as a subcontractors. The Project Leader from Company A (there might be few of those) is giving access/invites all the users for collaboration from companies B and C. Project leaders from Project X don't have automatic access to Project Y.
    Last edited by vos; 09-17-16 at 15:41.

  10. #10
    Join Date
    May 2016
    Posts
    81
    That's clear and I need to think if new information Will impact the cdm.


    Question:
    Who assign Project leader role to users

  11. #11
    Join Date
    Aug 2016
    Posts
    8
    Quote Originally Posted by informer View Post
    That's clear and I need to think if new information Will impact the cdm.


    Question:
    Who assign Project leader role to users
    This is done by the owner of the software. This could be a manual entry into a database for now. So let's say a superuser can assign the Project Leaders and the number of invites per Project.

  12. #12
    Join Date
    May 2016
    Posts
    81
    Hi Vos,

    I made 2 changes:

    1. Project entity is changed into category
    2. Company is now in relation with issue not with category with cardinalities (0,n) - (1,0) because category is just for qualifying an issue
    3. Assign relation is in relation with issue
    4. I added a relation between user and assign to store the leader project who assigned a role to other users


    CDM
    Click image for larger version. 

Name:	cdm_gym.jpg 
Views:	1 
Size:	117.9 KB 
ID:	17127


    LDM

    Click image for larger version. 

Name:	ldm_gym.jpg 
Views:	1 
Size:	139.8 KB 
ID:	17128

    Question:

    How is added a new issue? Does the the super user receive a demand from a project leader only ?
    Can a project leader access to all users of all companies by default or is the perimeter set up by the superuser?
    Last edited by informer; 09-20-16 at 06:13.

  13. #13
    Join Date
    Aug 2016
    Posts
    8
    Quote Originally Posted by informer View Post
    Hi Vos,

    Question:

    How is added a new issue? Does the the super user receive a demand from a project leader only ?
    Can a project leader access to all users of all companies by default or is the perimeter set up by the superuser?
    Hi,
    No, no. The super user 'gives' the Project Leader a Project entity and a certain amount of invites. And that is the end of Super User involvement here.
    Then Project Leader invites people from different companies to have access to the Project (and be able to use the software, which is going to be in the form of webapp). In effect the Project Leader initiates the creation of the other users' accounts (or access if they are already involved with other projects).
    The issue can be started/added by any user that is 'part' of (/has access to) the Project.

    Let's say, for simplicity sake, that you want to build an extension to your house and you want to set up a collaboration platform for the whole build. You would contact me (the 'superuser') to give you the ability to create a Project in 'my' software. You concluded that 11 people will be involved, so you also asked me for 11 invites. I give you 1 project and 11 invites within this project. You log in to your dashboard and you can start your project. You name it whatever you like, e.g. 'Informer's house extension 1'. And then you invite an architect and his/her assistant, an electrician, a plumber, a structural engineer, 2 joiners, 2 bricklayers, a painter and an interior designer. The invite is in a form of sent link and all you enter is: e-mail, company and role. (e.g. You want me as your plumber so you send an invite through a web form which will contain my e-mail: 'vos@example.com', company name: 'Vos Plumbers' and my role in the project: 'Plumber'). The link arrives to my e-mail and through accessing this link I finish the registration process by adding the rest of my details (i.e. name, surname, etc.). All the e-mails, in order to sent invites, are obtained directly from the people you want to include. The gathering of these details happens outside of the software, e.g. by e-mail, business cards, phone etc.
    Then all the Users you invited can start an Issue as well as comment on Issues started by other Users. All within 'Informer's house extension 1' project.

    Apologies for not coming back with the answers straight away, but I have started uni this week. Thank you for all the efforts you put into helping me. I am hoping to create an open source version of such collaboration software in the (near) future.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •