-
May 17th, 2016, 05:55 AM
#1
Thread Starter
Frenzied Member
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
-
May 17th, 2016, 06:44 AM
#2
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??
-
May 17th, 2016, 06:58 AM
#3
Thread Starter
Frenzied Member
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
-
May 17th, 2016, 07:06 AM
#4
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
-
May 17th, 2016, 07:34 AM
#5
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
hi,
Okay I understand. I will make new drawing tonight.
Thank you SZ
Regards,
Michelle
-
May 17th, 2016, 07:39 AM
#6
Re: Is this database structure correct? PART 1
Originally Posted by schoemr
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.
-
May 17th, 2016, 07:57 AM
#7
Thread Starter
Frenzied Member
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
-
May 17th, 2016, 08:01 AM
#8
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?
-
May 17th, 2016, 08:03 AM
#9
Thread Starter
Frenzied Member
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?
-
May 17th, 2016, 08:05 AM
#10
Thread Starter
Frenzied Member
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.
-
May 17th, 2016, 08:14 AM
#11
Thread Starter
Frenzied Member
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)
-
May 17th, 2016, 08:18 AM
#12
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...
-
May 17th, 2016, 08:43 AM
#13
Thread Starter
Frenzied Member
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
-
May 17th, 2016, 09:02 AM
#14
Re: Is this database structure correct? PART 1
Yes - that would be the case.
-
May 17th, 2016, 09:12 AM
#15
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
-
May 17th, 2016, 11:02 AM
#16
Thread Starter
Frenzied Member
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.
-
May 17th, 2016, 11:12 AM
#17
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.
-
May 17th, 2016, 11:20 AM
#18
Thread Starter
Frenzied Member
-
May 17th, 2016, 11:23 AM
#19
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
-
May 18th, 2016, 03:08 AM
#20
Thread Starter
Frenzied Member
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.
-
May 18th, 2016, 04:02 AM
#21
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?
-
May 18th, 2016, 05:02 AM
#22
Thread Starter
Frenzied Member
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.
-
May 18th, 2016, 08:16 AM
#23
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.
-
May 18th, 2016, 08:25 AM
#24
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
-
May 18th, 2016, 08:27 AM
#25
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?
-
May 18th, 2016, 09:12 AM
#26
Thread Starter
Frenzied Member
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
-
May 18th, 2016, 09:16 AM
#27
Thread Starter
Frenzied Member
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
-
May 18th, 2016, 09:22 AM
#28
Thread Starter
Frenzied Member
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.
-
May 18th, 2016, 09:28 AM
#29
Thread Starter
Frenzied Member
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.
-
May 18th, 2016, 09:32 AM
#30
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
-
May 18th, 2016, 11:45 AM
#31
Thread Starter
Frenzied Member
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.
-
May 18th, 2016, 11:48 AM
#32
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...
-
May 18th, 2016, 11:54 AM
#33
Thread Starter
Frenzied Member
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
-
May 18th, 2016, 12:43 PM
#34
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
-
May 19th, 2016, 07:53 AM
#35
Thread Starter
Frenzied Member
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.
-
May 19th, 2016, 08:37 AM
#36
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
-
May 19th, 2016, 09:07 AM
#37
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
Thanks Maz, i have to run they lock office building now. I will read carefully tonight
-
May 19th, 2016, 09:08 AM
#38
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...
-
May 19th, 2016, 09:17 AM
#39
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?
-
May 19th, 2016, 12:03 PM
#40
Thread Starter
Frenzied Member
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|