Can someone please help with my database structure?

[IkkeEnGij]

Michelle,
let me sum up some facts (at least they seem facts to me)

1) the system as it is now,just works
if it would not work
you would not be here
you would not even have a job at that company
that company would not even exist anymore, it would have gone out of busines long ago

2) the system as it is now, has problems
if it would not have problems, you would not be here

3) the problems the system has, have already been solved
if the problems would not have already been solved:
you would not have a job at that company
that company would not even exist anymore, it would have gone out of busines long ago


so for us to help you design a system that has no problems,and is easy to use, you will need to do:
or
1)hire a good systems analyst
or
2)make a systems analysis yourself

1 of cource is the most logical and the easyest
2 is not that difficult at all

all you have to do is;
1) make a good and very detailed description of how the system works now
2) make a good and very detailed description of the problems there are with the system now
3) make a good and very detailed description of how these problems are solved now

so i suggest you start a new topic where you put the above 3 things

may i also suggest what you should never do:
say 'i want to do it so and so'

what you should do is:
ask yourself (or us) how can i solve that problem in the most logical and easyest way

[szlamany]

[edit] keep working with FD... [/edit]

[FunkyDexter]

Schoemr has been quite clear about her requirements. She has detailed the existing system in post 1. All that was missing was the assessments table, the users table and the relationships to support them. She's been quite clear in what her requirements of those tables and relationships should be. Assessments belong to Production Areas. Users are assigned to Cities.

That's it. Requirements: done.

All the confusion has not come from Schoemr being unclear in her requirements but rather from other posters trying to introduce additional requirements she doesn't have. Gary's suggestion to keep user assignments over time is a sensible one in many systems and he was right to float the idea however Schoemr has identified that it is not a requirement of her system. The requirement to identify which user actually completed a particular assessment would be common to many systems and it's good that it was considered however Schoemr has identified that it is not a requirement of her system (or at least, it not an immediate requirement but may be added later as an additional feature).

The requirements have been done to death and I think Schoemr has a design (though it's yet to be displayed in full on the forum) that will fully meet those requirements. We owe here the courtesy of allowing her to either publish that design for our approval/criticism or to run with it under her own steam if she prefers. But the last thing that's needed at this stage would be to revisit the requirements in yet another thread.

[szlamany]

I'll back away!

When I see 40+ posts in a thread like this I start to get scared that direction was lost. As long as design is clear and spec is fleshed out it's time to make tables!

[FunkyDexter]

I'll back away!

Sorry, a bit of my frustration surfaced there - my bad.

The thing is, the requirements are understood at this stage. The design is 99% there - maybe 100%, we'll know for sure when schoemr posts back. And then it is, indeed, time to make tables. It would be such a waste if we looped all the way back to square one when we're so close to a solution.

[IkkeEnGij]

i'll back away to
but i hope nobody minds, when everything is worked out,
i'll be back with some use cases

[FunkyDexter]

i'll be back with some use cases

Why would you? The only people who should be bringing use cases to the discussion are schoemr and her users. The introduction of our own invented use cases has been one of the major impediments to progress in this and other threads.

[IkkeEnGij]

ring ring ring (telephone rings)
---good morning, with insurance company BestInsurance, what can we do for you

yes good morning, with TheDareDevils Company
we have a new headquarters and would like to have it insured against fire

---no problem after all we are known for beeing the best insurance company there is
---just give us your coordinates and we'll send an expert asap

ok,our adres is theBoulevardNoel Nr 45 Bloemfontein
our phone nr is 123-456-789, we are open from 8 AM to 5 PM

---Ok we'll send an expert asap, over and out, thank you
...lets see who is available
...oh,i see Gary and Michelle are available
...what a coinsidence Michelle is in Bloemfontein at the moment
...i'll call her
...hello Michelle there is a job for you, a fire risk assesment theBoulevardNoel Nr 45 Bloemfontein
...they are open from 8 AM to 5 PM and their phone is 123-456-789
Michelle:
ok, will go there first thing tomorrow
...ok ,michelle, thank you
...oh yes, as usual i have put the empty assesment already on our server
...its assesmentID is 789456
...and the coordinates are already filled in
...your userId is already filled in in the assements_users table, together with the assesmentID
...if you need assistence, Gary is available, just call him
...oh,yes if you work together,dont forget this time to fill him in to in the assements_users table
...and this time, if you work together,i do not want a war,understood ?
Michelle:
ok, just busines as usual

with all due respect

[IkkeEnGij]

the attendant reader of the above, will immediately have spotted that the phone taker has made a very big mistake
however his very big mistake has only created an itsy bitsy teeny weeny problem, that took Michelle only a few seconds to correct