Page 1 of 4 1234 LastLast
Results 1 to 40 of 151

Thread: Is this database structure correct? PART 1

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Is this database structure correct? PART 1

    Hi everyone,

    Please see my db structure below.

    Attachment 138057

    The green tables I think is correct:

    1 - A City can have more than one business
    2 - A Business can have more than one AreaOrPlant
    3 - A AreaOrPlant can have more than one Department

    I think this is fine?

    Now. The Yellow tables:

    1 - A Business can only have one Risk Owner
    2 - A Business can have many Users
    3 - A User can only have one Usertype (He can only be Admin, User, or Viewer)

    Is this structure correct?

    Also, a User can ONLY belong to ONE Business - This i dont know how to make?

    Many thanks,
    MvL

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    Green first...

    Give me some examples of DEPARTMENT values.

    Will the same type of department exist in different business/areaorplants??

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    Hi SZ,

    Give me some examples of DEPARTMENT values
    Production
    Electrical
    Instrumentation
    Mechanical
    Logistics
    Transport

    Will the same type of department exist in different business/areaorplants??
    Yes. You will find this departments in each business/areaorplants

    Thank you for your reply
    Michelle

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    Then is might be beneficial to have tblDepartment only be

    DepartmentId
    Department

    That way each of these items appears only once.

    Then you need an additional table - a JOIN table I believe it's most commonly referred to.

    tblAoPDepartment

    AreaOrPlantId
    DepartmentId

    And this is a two field table - with both of those fields being in a compound primary key. That PK keeps it so that only one entry of a DepartmentId can ever be made under an AreaOrPlantId

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    hi,

    Okay I understand. I will make new drawing tonight.

    Thank you SZ

    Regards,
    Michelle

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    Quote Originally Posted by schoemr View Post
    Also, a User can ONLY belong to ONE Business - This i dont know how to make?
    So this is a business requirement - and it makes sense.

    But I would imagine that a user can over time move to a different business.

    I would make the USER INTERFACE enforce the rule of what business a user is in.

    Otherwise you need to take UserId out of tblBusiness and either create a new JOIN table or simply put the BusinessId in tblUsers.

    We would need to discuss the life cycle of your data a bit more to give better advice in this area.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    Yes it is very possible that the user can move to a different business and also possible that user can relocate to another city, but it not important to keep history - meaning which user made what entries. It is important that users only do work in their assigned businesses. Meaning person in Texas not interfere with person in NYC

    We would need to discuss the life cycle of your data a bit more to give better advice in this area
    Thank you I been working on this for many weeks then discover I start incorrect. So it is difficult because I am learning and also many words I have to translate to and from English and sometimes I in hurry to ensure proper grammar then I am misunderstood. Now I have to make sure about my db structure. I do not wish to start over in a few weeks

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    Then the most simple way to deal with the user and business issue is probably to do as I said in that prior post - "put the BusinessId in tblUsers".

    Now a user can only ever be in a single business - right?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    I would make the USER INTERFACE enforce the rule of what business a user is in.
    Otherwise you need to take UserId out of tblBusiness and either create a new JOIN table
    or simply put the BusinessId in tblUsers.
    Which one of this 3 options you recommend?

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    Okay, got it!

    I can not make screen capture now, but tonight will post new diagram.

    Thank you very much.

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    SZ, I need confirm: BusinessID will thus have 2 relationships?

    1) BusinessID -----> tblAreaOrPlant (one to many)

    2) BusinessID ----> tblUsers (one to one)

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    You had this requirement in the OP

    2 - A Business can have many Users

    That means that the USER table must contain the business id - so that the business id can be repeated among many users - as stated in #2 above.

    I'm not sure what you mean by the relationships...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    I mean:

    In current diagram there is a one to many relationship between tblBusiness & tblAreaOrPlant (BusinessID in tblAreaOrPlant) this means that a Business can have more than one AreaOrPlant

    The 2nd relationship is between tblBusiness and tblUsers (not in diagram yet but as per your advice) where a Business can have many Users

    So a Business have:

    1 - More than one AreaOrPlant
    2 - More than one User

    So I asking if I understand correct that tblBusiness have 2 relationships

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    Yes - that would be the case.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    thank you SZ

  16. #16

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    Hi, here is revised structure:

    Attachment 138067

    The blue table is the new join table. Is this correct? Is the relation one-to-one?

    Many thanks,
    MvL

    ps. the relation from tblBusiness is as follow:

    1) tblBusiness to tblAreaOrPlant (one-to-many)
    2) tblBusiness to tblUsers (one-to-one) edit: (or must this be one-to-many as a Business can have many users?)

    The green tables I think is correct:

    1 - A City can have more than one business
    2 - A Business can have more than one AreaOrPlant
    3 - A AreaOrPlant can have more than one Department

    I think this is fine?

    Now. The Yellow tables:

    1 - A Business can only have one Risk Owner
    2 - A Business can have many Users
    3 - A User can only have one Usertype (He can only be Admin, User, or Viewer)
    Last edited by schoemr; May 17th, 2016 at 11:12 AM.

  17. #17
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    UserId must be removed from the tblBusiness as that would restrict a business to a single user.

    We moved BusinessId into tblUsers so that multiple users could share the same BusinessId.

    Of course this change also enforces the "user can only have a single businessid" rule.

    tblBusiness:BusinessId (one) ---> (many) tblAreaOrPlant:BusinessId (you have this one correct)
    tblBusiness:BusinessId (one) ---> (many) tblUsers:BusinessId (you have this one as (one) --> (one))

    tblAreaOrPlant:AreaOrPlantId (one) ---> (many) tblAOPDepartment:AreaOrPlantId

    tblAOPDepartment:tblDepartment(many) <--- (one) tblDepartment: DepartmentId
    Last edited by szlamany; May 17th, 2016 at 11:22 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  18. #18

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    thank you, i understand. I will revise again.

  19. #19
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    I had the last one with a copy/paste error - it's fixed above and appears here again corrected

    tblAOPDepartment:tblDepartment (many) <--- (one) tblDepartment: DepartmentId

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  20. #20

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    Hello SZ,

    Is this good now please?

    Attachment 138077

    Thank you so much for you time.

    Regards,
    Michelle

    ps. from department the structure will continue to "assessment" (this just risk assessment form). But before assessment there another table "context" where the assessment is given a name plus some other details. So a department can have many assessments. (this not in structure yet) So my idea is to conclude first part of db structure. Doing in stages. I'm just thinking to mention that...
    Last edited by schoemr; May 18th, 2016 at 03:14 AM.

  21. #21
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    Tell me some more about Risk Owner - who are they?

    The way you have it now that RiskOwnerId can be in lots and lots of different tblBusiness records.

    Is that modeling the reality of what is going on here?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  22. #22

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    hi...

    Tell me some more about Risk Owner - who are they?
    Every Business have one risk owner. This person have legal accountability in terms of various legislation. I not sure about USA, but e.g. in US a "CEO" have 'accountability' this person can transfer 'responsibility' but say something happens e.g. some grant event of fraud, then CEO will stand in court, therefore accountability cannot be transferred. This person may have given responsibilities to other managers, but ultimately he remains accountable. Also then a business cannot have more than one Risk Owner.

  23. #23
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    Your design will work in this regard.

    I guess the risk owner (CEO) could potentially be the risk owner at more then one business and your design will support that.

    You have it as 1 --> 1 and it's really 1 -- (many). All foreign key's in foreign files are 1--> (many) from what I see in your design diagram - this is the only one that is shown as 1 --> 1.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  24. #24
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Is this database structure correct? PART 1

    I would ask if the risk owner is also a User? If so would it make sense to have the risk manager be a True/False (Bit) on the User table and use that to the Business?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  25. #25
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    IF USERTYPE could be a value of RISK OWNER, it sure seems that could work.

    What does the FIELD (3rd on down) "BUSINESS" mean in your tblRiskOwner image?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  26. #26

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    Hi SZ,

    Or good morning? Here it is the end of the day now.

    I guess the risk owner (CEO) could potentially be the risk owner at more then one business
    No this will never be the case. Every business have a single risk owner. (legislation)

    Best Regards,
    MvL

  27. #27

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    hi Gary,

    I would ask if the risk owner is also a User?
    No. The "User" are way down in the hierarchy of organization. The users perform assessments. The risk owner will never do that.

    Best Regards,
    MvL

  28. #28

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    What does the FIELD (3rd on down) "BUSINESS" mean in your tblRiskOwner image?
    Okay I see my error (I think). It is same as "BusinessName" (in tblBusiness)

    ps. I wish to assign every business to a risk owner.. or maybe I not say correctly. Maybe assign every risk owner to a business?

    So a business:

    1 - can have many users (people that do assessments)
    2 - have only one risk owner (legal accountability)
    Last edited by schoemr; May 18th, 2016 at 09:26 AM.

  29. #29

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    I think... maybe have one-to-one relationship between RiskOwnerID and BusinessID?

    edit: my idea is to have a form where I enter risk owner details, and in that form have a combo with all the businesses, then for the risk owner I select (assign) him to a business by selecting the appropriate business from the combo. So I can make reports related to risk owners

    edit:

    also I wish to do the same for users. I want to assign a user to a business. Having a form with User details (Name, Lastname, phone, etc and have a combo with Businesses then select a business for that user. That's why;

    a business:

    1 - can have many Users
    2 - only one Risk Owner

    a User:

    1 - can have only one business



    So the hive can have many worker bees, but only one queen

    edit:

    for risk owner it nice to have. if not possible then okay, it is fine. but it very important that a user can only have one business (at a time) Or maybe I could have a form for every business and in that form specify who risk owner is?
    Last edited by schoemr; May 18th, 2016 at 09:48 AM.

  30. #30
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Is this database structure correct? PART 1

    No this will never be the case. Every business have a single risk owner. (legislation)
    Turn the question on it's head. Can a single individual be the risk owner for more than one business?

    Also, I'm fairly sure your User to User Type relationship should be one to many - e.g. there can be many users of a single type. Your fields are set up correctly for that but you've just put the wrong value on the diagram.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  31. #31

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    hello dex

    i dont have translator now so my english is be very horrible now

    Can a single individual be the risk owner for more than one business?
    the answer is No - 1 risk owner, 1 business.

    but when i driving to home now i think...... maybe i creating confusion for myself and people here trying to provide me advice. Because every business have only 1 risk owner maybe it is not needed to link it? i dont know. e.g. if i make a report for Business A, we know who the risk owner is. But it will be nice to have that.

    for me crucuially that a user can only belong to one business and business can have many users. I want admin person to be able to create users e.i. frmUsers. Enter all details and assign user to business by means of combo. Once that is done the user can only do assessments for the assigned business.

    Then User login. He already assigned to a business. i really really really need this function

    I see from the advice here that SZ and you see numbers when you observe the structure. e.g. "this ID will be many times in that table" etc. i must be true i never looked at it in such a way. I see names and i must learn to see the numbers

    so maybe, for now, i will remove risk owner? once i have the structure completed (part 1 and part 2) i will ask help for the issue of risk owner and where it must reside

    also i think it important to show table after departments. i must show that

    Michelle

    ps.

    Also, I'm fairly sure your User to User Type relationship should be one to many - e.g. there can be many users of a single type. Your fields are set up correctly for that but you've just put the wrong value on the diagram.
    thank you
    Last edited by schoemr; May 18th, 2016 at 11:48 AM.

  32. #32
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    From the description of Risk Owner is seems like that table can be eliminated until it gives a reason for being needed.

    RiskOwnerName should be a field within tblBusiness.

    I like the idea of seeing other tables now.

    Just keep what we have done so far as the model - it fits my KISS requirement - Keep-it-simple-stupid...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  33. #33

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    okay i will make the other diagram but only can be able to upload in the morning my pc here giving problems with screen captures (here in SA it now 7pm)

    Have a good day SZ


    RiskOwnerName should be a field within tblBusiness.
    krrrrrrr!! yes! I see thank you

  34. #34
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Is this database structure correct? PART 1

    so maybe, for now, i will remove risk owner?
    RiskOwnerName should be a field within tblBusiness



    Just so you know, I'm deliberately staying out of the thread because I think your previous threads have suffered from have too many people get involved. I'll chip in if I think it'll help but otherwise I'll stay quiet and let SzLamany lead - he's doing an excellent job of it.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  35. #35

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    Hi SZ,

    Here is the whole thing. I added 6 more tables.

    - Red table is final product. Textboxes are just calculations that is saved.
    - User complete "CONTEXT" form and start Assessment
    - Every CONTEXT can only have ONE assessment
    - Every Department can have many contexts (Think of context as the unique name of the assessment) In the end i want create a userform where a user can see list of his risk assessments (the way in which he named it) then he just double click on a name to open that specific assessment. (I am far away from that, I know) So a user must be able to start a new assessment and/or access assessments already done. It is highly important that a user can only access assessments for his allocated BUSINESS. If this not possible I have to abandon project.

    - Every department have equipment types e.g:

    1. Reactor
    2. Compressor

    - Every Equipment Type have many Equipment e.g:

    1. Reactor
    -Copper Based
    -Nikkel Based
    -Exothermic
    -Chemical
    -Liquid

    2. Compressor
    -Reciprocating compressors.
    -Ionic liquid piston compressor.
    -Rotary screw compressors.
    -Rotary vane compressors.
    -Rolling piston.
    -Scroll compressors.
    -Diaphragm compressors.
    -Air bubble compressor

    So every department have their own list of these above. I create a EQUIPMENT form with 2 DGV's. On tblAssessment form there a button to show this EQUIPMENT form. The user select Equipment Type on 1st DGV, and 2nd DGV update accordingly. The user double-click on selection and the two selected fields from the DGV's is transferred to Assessment form. The how to do this I am fine. I am just not sure if i place it correctly in structure. User can add and delete any of these as desired.

    Then, the grey tables...... This most certainty will present a problem. I have no idea.....

    This 2 tables work the same as for equipment (Userform with 2 DGV's) However, here is the problem: The first 10 risk types and associated risk names are generic. This means it is used within entire organization. User cannot modify this (I think i know how to do based on answer from jmcilhinney in other thread - i.e. prevent delete of ID's 1-10) But not a problem I can source solution I am sure.

    However....................

    Every department have Risk Types and Risk only associated to their specific departments. So user can add/delete to list, but not able to alter generic list. This i have no idea not even how to google solution.....I do not have any idea where to structure this grey tables. I not even know if this is possible.

    Once again, thank you so much for taking your time and effort.

    Dex, I understand


    Michelle

    Attachment 138113
    Last edited by schoemr; May 19th, 2016 at 08:03 AM.

  36. #36
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Is this database structure correct? PART 1

    I think you already have this: It is highly important that a user can only access assessments for his allocated BUSINESS. If this not possible I have to abandon project.

    If tblContent is an Assessment then in that table you have DepartmentID. That is linked to to tblAoPDepartement (via department ID) that links to tblAreaOfPlan via the AreaOfPlantID that has businessID that is also in tblUser so you can set the select based on the users BusinessID
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  37. #37

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    Thanks Maz, i have to run they lock office building now. I will read carefully tonight

  38. #38
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    If this statement is true

    - Every CONTEXT can only have ONE assessment

    Then you do not need to have a separate tblAssessment from tblContent.

    You are saying there is a 1-1 relationship anyway - right?

    And if you look you have no common fields - so your link is missing anyway.

    This area needs attention.

    The tblEquipmentTypes and tblEquipmentName confuses me.

    If EquipmentTypeId points to tblEquipmentName why then does the field EquipmentType appear in tblEquipmentTypes?

    If tblEquipmentTypes is a JOIN table then it should just have those two first fields and then everything "specific" to a piece of equipment goes into tblEquipmentName (you might want to rename these tables to be more clear).

    So - this area also needs attention.

    I see no reason for a tblCity - that's overkill in my opinion. You must have thousands of entries in your tblBusiness table - right? If you have 50 tblBusiness records then get rid of tblCity.

    K.I.S.S. vs. 3rd-normal-form

    I'll comment further shortly...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  39. #39
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Is this database structure correct? PART 1

    Ok - the rest of your post is 50 miles ahead of where the design car is sitting right now.

    Let's slow down.

    We need to design the equipment tables before figuring out how to assign them to departments - and from what I see now - none of the equipment keys are in the content or assessment tables - so we have a bigger issue - right?

    But let's ignore that for now - focus on the equipment...

    When you list 5 different types of reactors and a dozen types of Compressors a particular piece of equipment can only be a single "type".

    In other words you have either a Copper Based Reactor or a Liquid Reactor - right?

    I'm not talking about a department having more then one reactor - I'm trying to figure out how to model the "equipment" first.

    So - you must clarify if a piece of equipment is always a single TYPE or can it be two or three types?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  40. #40

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Is this database structure correct? PART 1

    Hi MZ,

    Then you do not need to have a separate tblAssessment from tblContent.
    Rationale for this I explain as follow:

    Now I have frmAssessment open (for tblAssessment), I enter all details, and click save. This record now saving and next is blank record. Now I must repeat Assessment Name and Date again. I wish to specify this only once. Plus after display diagram I realise i forgot another field when specify context. After Name of assessment should have another field "Responsible Person" (not same as risk owner - thus person more like a manager, not legal accountable) So even this I not wish to specify over and over again. Only once at start of assessment

    And if you look you have no common fields - so your link is missing anyway.
    so i need to put ContextID in tblAssessment? I was thought if one-to-one then such not needed. But probably I am wrong

    The tblEquipmentTypes and tblEquipmentName confuses me.
    Yes I see why, because i made error.

    It should be:

    tblEquipmentType

    - EquipmentTypeID
    - Equipment Type

    tblEquipment

    - EquipmentID
    - EquipmentTypeID
    - Equipment Name

    With one-to-many relation between EquipmentTypeID in each table. I unable to make screen captures at home. I will do tomorrow morning at work.

    I see no reason for a tblCity
    This reason because in Texas you can have Wall Mart but in NYC and Washing too. So if user just select Wall Mart, then which one?

Page 1 of 4 1234 LastLast

Posting Permissions

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



Click Here to Expand Forum to Full Width