-
May 27th, 2016, 10:10 AM
#121
Re: Is this database structure correct? PART 1
oh,OK,i see
must have missed that (or rather must have forgotten it)
thanks
EDIT: lets see if i indeed see it
a Business can have 0(1) or more AreaOrPlants
an AreaOrPlant can have 0(1) or more Departments
a Department can have 0(1) or more Contexts
a Context can have 0(1) or more Assesments (Context_Details)
so one can say that a user does a risk assesment for a particular context
is that correct ?
Last edited by IkkeEnGij; May 27th, 2016 at 10:57 AM.
Reason: do i see it ?
do not put off till tomorrow what you can put off forever
-
May 28th, 2016, 04:29 AM
#122
Re: Is this database structure correct? PART 1
to answer my own question:
a Department can have 0(1) or more Contexts
yes, that is correct
not only is it correct,it is even correct the other way around:
a context can have 0(1) or more Departments
so the question is:
will there be an assesment specific to each department-context
or will the assesment include every department it applies to ?
example:
the context is:Assessment for Reaction Unit at Section 101B
Reaction Unit at Section 101B has a shed with a big gear pump in it
that big gear pump belongs to the mechanical department
Reaction Unit at Section 101B also has an electric motor to drive the big gear pump
that electric motor pump belongs to the electrical department
will both the big gear pump and the electric motor be included in the assesment for Reaction Unit at Section 101B
or will there be 1 assesment relating to the electrical department, and 1 assesment relating to the mechanical department
iow:
do the assesments taking experts even mention the department into their assesments ?
because the department can be deducted from the equipment (the big gear pump in this case)
do not put off till tomorrow what you can put off forever
-
May 28th, 2016, 06:35 AM
#123
Re: Is this database structure correct? PART 1
How can a context have 0(1) or more Departments?
Each tblContext row is associated with a single EquipmentDescriptionID
That links to the tblEquipmentDescription table where each unique piece of equipment is represented.
That is why I have those silly descriptions - so as to show that this table is not a lookup table but represents all the stock of actual equipment.
And of course in this tblEquipmentDescription table is both an AreaOrPlantId and a DepartmentId.
With that chain - each piece of equipment is owned by a Department within an AreaOrPlant.
I intentionally flattened department to be more or less a demographic piece of information - such as gender or eye color. I wanted to avoid having a whole set of tables to describe which departments existed in an AreaOrPlant.
Departments have system wide values - more or less lookup table.
AreaOrPlant is a real value associated with a unique physical location.
I intentionally started with equipment in the table design - as that is real and easy to think of (back in post #77).
Then in post #96 I moved into the business and user side of the table design (only after we made sure the equipment was correct).
And of course the context and assessment only came on after all that was verified and cleaned up.
Last edited by szlamany; May 28th, 2016 at 06:42 AM.
-
May 28th, 2016, 11:05 AM
#124
Re: Is this database structure correct? PART 1
1)
context:Reaction Unit at Section 101B
Reaction Unit at Section 101B has a big gear pump, that big gear pump belongs to the mechanical department
Reaction Unit at Section 101B has an electric motor to drive the big gear pump,that electric motor belongs to the electrical department
2)
context:Reaction Unit at Section 123A
Reaction Unit at Section 123A has a big gear pump, that big gear pump belongs to the mechanical department
Reaction Unit at Section 123A has an electric motor to drive the big gear pump,that electric motor belongs to the electrical department
so:
a context can have equipment from more than 1 department ...right ?
equipment from 1 department can be used in more than 1 context ...right ?
so, there is a many to many relationship between context and department ...right ?
ok,it is not a defined relationship,it is an implied relationship, but all the same, the relationship is there
Steve, i am not questioning your design, your design works. period (see footnote 1)
i am wondering why Michelle does not want to show some risk assesments made by some of the experts, maybe some of herself ?
i really am afraid she may be heading towards a total failure
if people are used to do certain things,in certain ways,forcing them to do it differently,will meet with a lot of resistance
do not under estimate that resistance, it can go from mild embarasment to rightout sabotage
some possible risk assesment scenarios:
Michelle is asked by the mechanical department of western co to do a risk assesment
so for each context she does a risk assesment for the equipment of the mechanical department that is used in that context (seems logical to me)
so if the context is Reaction Unit at Section 101B, she only makes an assesment for the big gear pump
if asked by any other department she does a risk assesment for the equipment of that department that is used in that context (seems logical to me)
so if the department is electrical and the context is Reaction Unit at Section 101B, she only makes an assesment for the electric motor
but now Michelle is asked to do a risk assesment by an AreaOrPlant of western co
what does she do ?
1)she makes for each department of that AreaOrPlant an assesment specific to that department (as above)
or
2)she includes every department of that AreaOrPlant in an assesment
so if the context is Reaction Unit at Section 101B
she makes an assesment for the big gear pump and the electric motor
so to ensure that the new way of working for the users, makes as few as possible changes to the old way of working for the users
we have to know in a very intimate way how the risk assesments are made now by the users
and how are the risk assesments, made in situ, going to be transferred to the central database ?
(i think it was GaryMazzone who made a very important remark about identity colums)
footnote:
Steve, i am not questioning your design, your design works. period
but i think the tblUsers has a serious design flaw (but can be easely remedied)
once a user is assigned to another business, there is no way to know what user made what risk assesment
Last edited by IkkeEnGij; May 28th, 2016 at 11:12 AM.
Reason: bolded 'now'
do not put off till tomorrow what you can put off forever
-
May 28th, 2016, 12:43 PM
#125
Re: Is this database structure correct? PART 1
Originally Posted by IkkeEnGij
1)
context:Reaction Unit at Section 101B
Reaction Unit at Section 101B has a big gear pump, that big gear pump belongs to the mechanical department
Reaction Unit at Section 101B has an electric motor to drive the big gear pump,that electric motor belongs to the electrical department
2)
context:Reaction Unit at Section 123A
Reaction Unit at Section 123A has a big gear pump, that big gear pump belongs to the mechanical department
Reaction Unit at Section 123A has an electric motor to drive the big gear pump,that electric motor belongs to the electrical department
so:
a context can have equipment from more than 1 department ...right ?
equipment from 1 department can be used in more than 1 context ...right ?
so, there is a many to many relationship between context and department ...right ?
ok,it is not a defined relationship,it is an implied relationship, but all the same, the relationship is there
I think that the granularity of a piece of equipment is not as stated above. If there is a pump and an electric motor they are two different pieces of equipment but in the same AreaOrPlant.
Why not take a copy of the script and put your suggested data into the INSERT's and ask questions using tangible data we can play with in SSMS.
if people are used to do certain things,in certain ways,forcing them to do it differently,will meet with a lot of resistance
do not under estimate that resistance, it can go from mild embarasment to rightout sabotage
some possible risk assesment scenarios:
Michelle is asked by the mechanical department of western co to do a risk assesment
so for each context she does a risk assesment for the equipment of the mechanical department that is used in that context (seems logical to me)
so if the context is Reaction Unit at Section 101B, she only makes an assesment for the big gear pump
if asked by any other department she does a risk assesment for the equipment of that department that is used in that context (seems logical to me)
so if the department is electrical and the context is Reaction Unit at Section 101B, she only makes an assesment for the electric motor
but now Michelle is asked to do a risk assesment by an AreaOrPlant of western co
what does she do ?
1)she makes for each department of that AreaOrPlant an assesment specific to that department (as above)
or
2)she includes every department of that AreaOrPlant in an assesment
so if the context is Reaction Unit at Section 101B
she makes an assesment for the big gear pump and the electric motor
so to ensure that the new way of working for the users, makes as few as possible changes to the old way of working for the users
we have to know in a very intimate way how the risk assesments are made now by the users
and how are the risk assesments, made in situ, going to be transferred to the central database ?
(i think it was GaryMazzone who made a very important remark about identity colums)
Good questions - we need to challenge the design to make sure it fits what it is modeling.
but i think the tblUsers has a serious design flaw (but can be easely remedied)
once a user is assigned to another business, there is no way to know what user made what risk assesment
I would imagine that if a user leaves a location they should NO LONGER have access to those assessments. That is an assumption on my part though. If John makes assessments in 2015 and leaves for the other side of South Africa to work in a different BusinessId does he still need to now be able to see those 2015 assessments? Or do they belong to the BusinessId and can be reviewed by any NEW PERSON assigned to that business?
[edit] in looking further at the tables we have so far I see that both the equipment and the users belong to the business. That means that if two or three users are at a business they can see all assessments at the business. Regardless of what user has entered them. I could review prior posts here in the thread to see if I can find clear reference to that being the business requirement - or we could wait for Michelle to come back online and see these posts [/edit]
Last edited by szlamany; May 28th, 2016 at 12:47 PM.
-
May 30th, 2016, 01:46 PM
#126
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
Hi IkkeEnGij,
so one can say that a user does a risk assesment for a particular context
is that correct ?
yes this is correct.
So what is "Context"?
Shortly a context is like putting a "name" to the assessment. e.g.
Code:
1 - Production Assessment for Distillation Section 600 (for production risks) - May 2014
2 - Electrical Assessment for Distillation Section 600 (for electrical risks) - June 2012
3 - Electrical Assessment for Distillation Section 600 (for electrical risks) - September 2013
4 - Electrical Assessment for Distillation Section 600 (for electrical risks) - November 2014
5 - Production Assessment for Distillation Section 600 (for production risks) - June 2015
6 - Production Assessment for Reactor Unit 101B (for production risks) - Jan 2015
7 - Mechanical Assessment for Reactor Unit 101B (for production risks) - Jan 2015
Then under each name there own list of equipment. And user can click on any of "names" above to see related records
Steve
I would imagine that if a user leaves a location they should NO LONGER have access to those assessments.
Yes this is correct.
That means that if two or three users are at a business they can see all assessments at the business.
Yes this is correct. Same business users work together. But users from different business not interfere with each other work.
IkkeEnGij
once a user is assigned to another business, there is no way to know what user made what risk assesment
This not matter. The user just facilitate the assessment. After assessment gets printed and signature for Risk Owner. So who punch keyboard not really required. Someone signs to accept results. But it can be nice to know. Maybe I can just add such information in context (which is start of assessment) e.g.
1 - Production Assessment for Distillation Section 600 (for production risks) - May 2014 - Michelle
2 - Electrical Assessment for Distillation Section 600 (for electrical risks) - June 2012 - Steve
3 - Electrical Assessment for Distillation Section 600 (for electrical risks) - September 2013 - IkkeEnGij
Please execute for I taking long to go online.
Best Regards,
Michelle
-
May 30th, 2016, 01:53 PM
#127
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
I would imagine that if a user leaves a location they should NO LONGER have access to those assessments. That is an assumption on my part though. If John makes assessments in 2015 and leaves for the other side of South Africa to work in a different BusinessId does he still need to now be able to see those 2015 assessments? Or do they belong to the BusinessId and can be reviewed by any NEW PERSON assigned to that business?
They belong to BusinessID and can be reviewed by any new person assigned to that business. All this above is correct Steve
-
May 30th, 2016, 02:07 PM
#128
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
so the question is:
will there be an assesment specific to each department-context
or will the assesment include every department it applies to ?
example:
the context is:Assessment for Reaction Unit at Section 101B
Reaction Unit at Section 101B has a shed with a big gear pump in it
that big gear pump belongs to the mechanical department - TRUE
Reaction Unit at Section 101B also has an electric motor to drive the big gear pump
that electric motor pump belongs to the electrical departmentb - TRUE
will both the big gear pump and the electric motor be included in the assesment for Reaction Unit at Section 101B - NO
or will there be 1 assesment relating to the electrical department, and 1 assesment relating to the mechanical department - YES
iow:
do the assesments taking experts even mention the department into their assesments ?
because the department can be deducted from the equipment (the big gear pump in this case) - I see what you think. But can be thousands of equipment, and not always possible to make thinking of to what department every piece equipment belongs to just looking at name. That is what every department make list of own equipment
Please see my say in red.
Best Regards,
Michelle
-
May 30th, 2016, 02:10 PM
#129
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
if people are used to do certain things,in certain ways,forcing them to do it differently,will meet with a lot of resistance
do not under estimate that resistance, it can go from mild embarasment to rightout sabotage
i will puch them very hard!
edit: but i think this much much MUCH better than we before use MS Excel...
-
May 30th, 2016, 02:16 PM
#130
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
If there is a pump and an electric motor they are two different pieces of equipment but in the same AreaOrPlant.
that is correct Steve
-
May 31st, 2016, 03:52 AM
#131
Re: Is this database structure correct? PART 1
ok, i think i am beginning to see how things are done:
the big picture:
<Western Co> ask <Michel Co> to do a risk assesment (for <Western Co>)
that risk assesment is only for internal use by <Western Co>
there is no third party involved -footnote 1
<Michel Co> has no responsability whatsoever once <Western Co> has ok-ed the assesment
the experts of <Michel Co> are in fact only helping the safety officer of <Western Co>
it is only the safety officer of <Western Co> that is responsable
all the <Michel Co> experts use ms excel to do the assesments
and they all keep a copy of the assesment on their own computer
and they all would like to continue to work as they have always done
but now <Michel Co> would like to have those assesments centralised
to identifie an assesment the following is needed
1)The business the assesment is for
2)the areaorplant the assesment is for
3)the department the assesment is for
4)the context of the assesment
5)maybe the date ?
i think that the easiest way to centralise those assesments is simply: -footnote 2
have a table with those 4 (5) fields, and a field that uniquely identifies the assesment
and store the .xls-files in a blob table field with that unique identifier as name
(by unique identifier i do not necessarely mean a guid)
footnote 1:
by 'there is no third party involved',i mean
there is no <Insurance Co> that asks <Michel Co> to do a risk assesment on <Western Co>
footnote 2:
i said the easiest way,not the best way
do not put off till tomorrow what you can put off forever
-
May 31st, 2016, 05:07 AM
#132
Re: Is this database structure correct? PART 1
to identifie an assesment the following is needed
1)The business the assesment is for
2)the areaorplant the assesment is for
3)the department the assesment is for
4)the context of the assesment
5)maybe the date ?
All that would be required would be the assessment id. From that you can infer the context. From the Context you can infer the piece of equipment in question (ie EquipmentDescription) and the date. From the Piece of Equipment you can infer the AreaOrPlant and the Department and from the AreaOrPlant you can infer the business.
That's assuming there's even a requirement to hold the excel document. Nobody's asked for it and, as I understand it, the whole purpose of the exercise is to remove that way of working.
i will puch them very hard!
Ha! Spoken like a true DBA.
Edit>Steve, at this point I would offer up only one (very pedantic) criticism. I don't like the name EquipmentDescription as that sounds more like a look up table to me. I'd go for something more "instance based". It's a trivial point, though, and doesn't change the design. You can ignore it as long as you and Michelle are on the same page.
Last edited by FunkyDexter; May 31st, 2016 at 05:16 AM.
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 31st, 2016, 10:21 AM
#133
Re: Is this database structure correct? PART 1
Hi funcky,
either
1) you have not understood my post
or
2) michelle has given you some of the .xls files the experts now use to make their risk assesments
and by incredible chance these .xls files, in some way, are an exact mirror, of the database Steve has designed
i think 2 is a bit unlikely
for, if that was the case, i suppose michelle would not be here, since an exact mirror of Steves's database would already be in use by her business
with the system i propose nothing would have to change for the experts, just business as usual
and the risk assesments would be centralized and easely accessible
but hey, who am i, to tell anyone what to do
to cite Alfred E.Neuman: What me worry
do not put off till tomorrow what you can put off forever
-
May 31st, 2016, 10:45 AM
#134
Re: Is this database structure correct? PART 1
Originally Posted by FunkyDexter
Edit>Steve, at this point I would offer up only one (very pedantic) criticism. I don't like the name EquipmentDescription as that sounds more like a look up table to me. I'd go for something more "instance based". It's a trivial point, though, and doesn't change the design. You can ignore it as long as you and Michelle are on the same page.
I also dislike that name for the exact same reason.
Michelle seemed set on it - I'm expecting it looks different translated into whatever language she is using.
-
May 31st, 2016, 12:17 PM
#135
Re: Is this database structure correct? PART 1
Michelle seemed set on it - I'm expecting it looks different translated into whatever language she is using
Yeah, I figured. As long as you're both on the same page I'd stick with the wording Michelle/the business currently understands.
Ikkengij, Michelle has stated a couple of times that this is to replace the current Excel system they're using and centralise it. There wouldn't be much point in the exercise otherwise. And she described contexts and assessments in post 109. I'll admit the language barrier makes it a little tricky to follow at times but it looks like Steve's teased out all the correct details to me.
Last edited by FunkyDexter; Jun 1st, 2016 at 08:27 AM.
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 31st, 2016, 12:23 PM
#136
Re: Is this database structure correct? PART 1
I see she also asked about Crystal reports in another thread - so I would imagine the database will turn into a source of better reporting.
-
May 31st, 2016, 04:43 PM
#137
Re: Is this database structure correct? PART 1
first thing i would like to make clear:
whenever i used the word 'assesment' in post #131, i meant 'risk assesment'
i understand that could have caused confusion
ok,everyone knows by now that my memory is not so good (to put it mildly)
but i just cannot remember Michelle ever stating that this is to replace the current Excel system
all i can remember is her saying that the current system is difficult to use,
because the right hand does not know what the left is doing
and the experts are keeping their risk assesments on their own computer, and not centralized
in fakt i dont remember Michelle ever mentioning Excel, prior to yesterday in post #129
in fakt i asked Michelle several times to show some risks assesments made by some experts...she never did
i can imagine excel not being such a bad tool for making a risk assesment
i can image worksheets with a row containing a 'context'
and each row under with a 'risk',and all the other fields such as ProbabilityScore, ConsequenceScore,etc...
i can even imagine that .xls file, as a report
in fakt if the worksheets are interpreted as context's,then the exel file is the report (as wel as the risk assesment)
but if it really is to replace the current Excel system
all there is so far, is a database design made by Steven
as far as i can tell, nobody has made an attempt to even think how the risks assesments made by the experts
are going to be transferred to that database
or how the experts are going to make their risk assesments
how long is this going on already ?
and Michelle has not even a proof of concept
please consider this as possitive criticism
do not put off till tomorrow what you can put off forever
-
Jun 1st, 2016, 03:58 AM
#138
Re: Is this database structure correct? PART 1
in fact, now that i know that the experts make their risk assesments in excel
(do they really ?, confirmation please)
i think it would be very hard for someone to do a risk assesment in excel
that is not at the same time a very good report
so why would someone, something, what is all that is needed at the same time
(namely a risk assesment and a report at the same time)
tear into its constituent pieces, put the pieces into a database
only to put these pieces back together into a report ?
all this assumes of cource that all the experts use the same system to make their reports in excel
oh,btw: i am willing to bet with anyone, for a box of chokolates,
that the risks assesments the experts make now in excel
are incompatible with the database Steve did put here
(and that is not Steve's fault)
do not put off till tomorrow what you can put off forever
-
Jun 1st, 2016, 08:19 AM
#139
Re: Is this database structure correct? PART 1
Michelle, I apologise if I'm derailing yet another of your threads. I'm trying very hard to avoid it because you deserve better.
Ikkengij, Michelle mentioned they used to work with Excel in post 129 and in the same post she made it abundantly clear that her intention was to move them away from this approach. You may disagree with that decision but we're here to serve her requirements, not yours.
You keep introducing hypothetical requirements which Michelle has not expressed. Everything from keeping a user audit history on assessments, to working detached, to storing excel documents. You have followed her from thread to thread repeating exactly the same pattern. At one point you even suggested she hire a professional systems analyst to do this for her. There are an infinite number of hypothetical requirements we could introduce but they won't help guide her to a satisfactory outcome. I know you are trying to help but, at present, you are having the exact opposite effect.
I asked way back on page 2 that everyone step back from this thread and allow Steve to lead because too many competing voices were getting in the way of Michelle achieving a resolution. Since then Michelle and Steve have made great progress toward achieving Michelle's goal. I will make the same request again because yours and my contributions to this thread are not useful to Michelle: Let's both step out.
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
-
Jun 1st, 2016, 09:11 AM
#140
Re: Is this database structure correct? PART 1
Funky, you are 100% right
i'll back out
if i did hurt anybody my sincere apologies
do not put off till tomorrow what you can put off forever
-
Jun 2nd, 2016, 02:49 AM
#141
Re: Is this database structure correct? PART 1
No worries and my apologies if I came across as aggressive or dismissive. As I said previously, I don't mean to belittle anyone in this thread's abilities. I just want to keep the voices to a minimum so that Steve and Michelle can get on with 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
-
Jul 11th, 2016, 02:21 AM
#142
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
Hi Steve,
I make apology for absent. Just to say shortly in this time my father passed on. It make very sad time for us and also we are now moving for the city as it not safe alone on the farm anymore. It will takes very months to finalize my father's "boedel" I not know word. affairs?
Hopefully I will have internet installing this week at new house. I wish we to continue this project.
Kind Regards,
Michelle
-
Jul 11th, 2016, 04:50 AM
#143
Re: Is this database structure correct? PART 1
Michelle,
So sorry to hear about your loss. My thoughts are with you and your family.
I'll be looking forward to working on your project whenever you are ready.
Steve
-
Jul 28th, 2016, 01:13 AM
#144
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
Hi Steve,
I hope you be well?
So I can pay focus now and I wish to for us to continue at same point where we talk about in past. Also now pressure from work is better as before. I want to make new tables because now it much changed to what I make at start of this thread. My first idea is to make new diagram and post here. I am very happy to see this thread make almost 3000 views. Do you think I can make PART 2 now in new thread or we shall continue on this thread?
Thank you for patience you have. I think everything will be better now.
Sincerely yours,
Michelle
-
Jul 28th, 2016, 04:29 AM
#145
Re: Is this database structure correct? PART 1
Great to hear you are back!
If you want to make another thread to start back up that makes sense to me.
Looking forward to working with you!
-
Aug 4th, 2016, 01:41 PM
#146
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
Hello Steve,
Okay i make new thread
I was asking to by someone i can not provide answer for. Do you know Steve? In MS SQL Server Express where i intention to make my database (with you) how many people can connection to server at once? What happened when 10 different people press save button from different location?
ps. i start to make new tables i will publish in new thread saturday or sunday
Best wishes,
Michelle
-
Aug 4th, 2016, 01:50 PM
#147
Re: Is this database structure correct? PART 1
You will get this is a DBA answer It Depends.... Are they all new records? Are people trying to edit the same record? If all new you will get 10 new records. If they are editing the same record last one there wins.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Aug 4th, 2016, 01:58 PM
#148
Thread Starter
Frenzied Member
Re: Is this database structure correct? PART 1
no no i mean can many people make connection to server at once. from different location, so each person working on own assessment. not same records
-
Aug 4th, 2016, 02:35 PM
#149
Re: Is this database structure correct? PART 1
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Aug 4th, 2016, 04:43 PM
#150
Re: Is this database structure correct? PART 1
I have client sites with dozens of users going against MS SQL Express - never heard any complaints.
When I have clients with more users - hundreds - usually they go with bigger servers.
And they also go with paid versions of MS SQL. The Express version will not use all processor cores, for instance.
-
Aug 5th, 2016, 08:56 AM
#151
Re: Is this database structure correct? PART 1
One processor and 1 GB of memory only for Express version
Sometimes the Programmer
Sometimes the DBA
Mazz1
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
|