proper database design vs front end usability

[Richie27]

Hi,



I have a table called file with fields as follows

*staff member selected from combo box that pulls the staff from a table which basically has just their name and staff number - stores just the id field.

file_id
file_no
file_archiver*
file_archival_authorisation*
file_long_term_archiver*
file_long_term_archival_authorisation*
file_destruction
file_destroyer_id (fk)
file_destruct_auth*

Originally, I had file destruction, file archival and file authorisation in separate tables - before I thought about modeling it. file destruction was the one side of a one to many relationship as many files could be destroyed in one instance, archival authorisation was similar and was connected to both the staff and the file tables and archival was another such table.

However, I was then told that the design was required to be in a single form so, not seeing how my original design was workable within that frame - I brought the fields back in to the file table - which I could see causing possible problems down the road.

Any ideas what would be the best way to model this and is the above table design a very bad idea?

Thanks in advance.

[TinaCa]

It looks like your Staff table is only a lookup table and should be separate. it looks like you have different staff members for different tasks (archiver, destroyer, etc). If you put this in separate tables your Staff table would need to be the first table in a query with a right join to all the others. this would allow you to put them on the same form. However, if you need to update them (add, change, etc the information on a single form) then all the fields need to be in the same form. You can still use the Staff table as a lookup (using combo box for each staff member field on the form) and it is OK to repeat the staff member Id in the table for each line item.

Hope that helps.

[ssanfu]

Will this structure work for you? Look at the relationship window.

[Richie27]

It looks like your Staff table is only a lookup table and should be separate. it looks like you have different staff members for different tasks (archiver, destroyer, etc). If you put this in separate tables your Staff table would need to be the first table in a query with a right join to all the others. this would allow you to put them on the same form. However, if you need to update them (add, change, etc the information on a single form) then all the fields need to be in the same form. You can still use the Staff table as a lookup (using combo box for each staff member field on the form) and it is OK to repeat the staff member Id in the table for each line item.

Hope that helps.

Thanks for that - I appreciate it.

I really only need to record the names of staff. The file table is the most important as it is a kind of records management database. There will really only be a handful of staff members one who will be authorising archival and one who will be physically doing it. I've just recorded their name, staff number and job title in the table I'm using as a lookup.

When you say about "all fields need to be in the same form" would the same be true for the underlying table? So is it acceptable to do what I have done do you think?

This "One form" thing does make the interface less complex but I worry about dragging those fileds I previously mentioned (lt_archival, authoriser etc) back into the file table.

The main structure at present has a form with a client table as the main form and the file table as a subform of the client table as one client will have many related files.

[Richie27]

Will this structure work for you? Look at the relationship window.

Thanks for that detailed response. That is certainly educational. However - client does not like having to open multiple forms and wants all in a single form as they feel it is too confusing for users to have to open multiple forms.

Also, my main concern is a client table for which there will be multiple files so I have files on the service user table via a one to many relationship. Attached is what my db looks like. It may clarify what I am talking about as I am aware it is diffcult to diagnose without a full picture.

I have nearly finished my dbase but I am having last minute doubts!

[Richie27]

There is indeed much to be learned from the way you organised that db - how did you do the layout in form 1 - I like that!

[Richie27]

Okay - this is my original schema which is way more normalized - couldn't for the life of me figure out how to get it all on one form though!

Is it even possible.

Thank god for such forums and the lovely helpful people on it!

[ssanfu]

I think you still need work on the table structure to get it in 3NF.

client does not like having to open multiple forms and wants all in a single form as they feel it is too confusing for users to have to open multiple forms.

Really??? The client doesn't think they have intelligent people working for them???


Well, it is possible that you could get it on one form by using subforms in multiple tabs. One tab w/subform for check in/out, one tab w/subform for archiving, one tab w/subform for LT archiving, one tab w/subform for destruction. I still think these are all "events" and should be in one table, but ..... (my $0.02 )

Also, there are 5 tables with a field named "ID" .... gets confusing when creating queries. I make the PK (as much as possible) include the table name, like the checkout table.

[Richie27]

I think you still need work on the table structure to get it in 3NF.


Really??? The client doesn't think they have intelligent people working for them???


Well, it is possible that you could get it on one form by using subforms in multiple tabs. One tab w/subform for check in/out, one tab w/subform for archiving, one tab w/subform for LT archiving, one tab w/subform for destruction. I still think these are all "events" and should be in one table, but ..... (my $0.02 )

Also, there are 5 tables with a field named "ID" .... gets confusing when creating queries. I make the PK (as much as possible) include the table name, like the checkout table.

Hey thanks - the events table thing does make a lot of sense. Aside from that - do you think (org schema) some of the other fields in say the service_user table like contact details should ideally be broken out into other tables? I guess I've been cutting corners with normalisation because of front end design trickiness.

One more thing - since file destruction, archival, lt_archival would have slightly different fields to file_checkout - do you think I should break the different fields (that are in file_checkout) out into another table.

Sorry for being so incredibly dumb - but I've very amateur and I got no place else to go! ;-)

Thanks for all your help so far - it is much appreciated.

I will spread the karma if I ever manage to get out of this pit of ignorance!

[Richie27]

So - is this better do you think?

[ssanfu]

do you think (org schema) some of the other fields in say the service_user table like contact details should ideally be broken out into other tables?

I have no idea what the purpose of your data base is for, so you might take my suggestions with a grain of salt.

Looking at the table "service_user", these seem OK:
--fields--
ref_no (I might have used su_no_PK or Serv_User_PK)
surname
first_name
home_address
date_of_birth (maybe save some typing and use DOB?)
date_of_death
date_of_leaving_service

Don't know about these
- can there be more than one of each of these?
- How are these an attribute of a person?

residential_service
home_service

date_of_last_contact
details_of_last_contact

-----------------------------
Here are web pages about normalization :
http://www.jpmartel.com/bu12_c.htm - aimed at dBase, but still valid
http://pubs.logicalexpressions.com/P...icle.asp?ID=88
http://pubs.logicalexpressions.com/P...cle.asp?ID=182

[Richie27]

sorry mate. the new.doc one - attached in my previous post was the latest schema. Though I just spotted a flaw in that too as in the events table destruction is done by a company rather than a person and I didn't move the destruction bits over.

Ya, I guess the contact thing could be a problem where it is but I thought it might be okay sense there's only ever going to be one entry there, apparently.

thanks for your help anyway.

[Richie27]

sorry mate. the new.doc one - attached in my previous post was the latest schema. Though I just spotted a flaw in that too as in the events table destruction is done by a company rather than a person and I didn't move the destruction bits over.

Ya, I guess the contact thing could be a problem where it is but I thought it might be okay sense there's only ever going to be one entry there, apparently.

thanks for your help anyway.

Nice tutorials by the way - cheers!

[ssanfu]

Got caught up in work and missed the latest attachment.

Changed the table structure 6 times on one of my mdb projects.. Seemed pretty straightforward... but wasn't providing the results needed.

Got to keep tweaking the structure until the results are right. Good luck.

[Richie27]

Got caught up in work and missed the latest attachment.

Changed the table structure 6 times on one of my mdb projects.. Seemed pretty straightforward... but wasn't providing the results needed.

Got to keep tweaking the structure until the results are right. Good luck.

Ha, ya - I keep changing too.

I could have it looking really nice and they would be happy with it but I know in my heart that it is fundamentally flawed.

Bye bye Mr. Magician and your bag of tricks!!