Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

data modelling best practices

Posted on 2013-06-24
9
Medium Priority
?
407 Views
Last Modified: 2013-07-21
guys, this question is an offshoot of another question titled "No primary key for table benefits". the discussion there skewed off into data modelling and thus here is a question specifically for that. JDettman and Bitsqueezer made some significant, significant contributions there in terms of helping us understand data modelling and a few other things about access memory etc, so be sure to check it out!

ok so let me start my question proper! = )

Bitsqueezer, here's your post below
Hi,

I've sorted the chaotic data model a little bit in the attachment. You should always try to create a data model without crossing lines as good as possible, makes it more readable.

Although your model looks a little bit better now I would say there are significant errors in the relations because multiple tables are linked together on multiple ways which mostly is not needed. But I cannot say more because I really cannot see what the target or sense of this database should be.
Moreover I think it would get far away from your initial question to discuss your data model in this thread.

For your other questions about the background of what is saved in pages I think Jim can answer that better than me.

Cheers,

Christian

thanks for your lengthy and very comprehensive response first and foremost!! = )

1) what is the difference database model and database schema?
2) could you touch a bit more on what you mean by
multiple tables are linked together on multiple ways which mostly is not needed
? which tables in the attached database are we talking about and what are some fixes for that?

this database is mainly a workflow database which 1) automates work through VBA, 2) as it does the work it triggers custom events which log the work by codes for example H:ACT-R standing for home, account, reconciliation or other codes like that.

i know it might be a bit vague, but am happy to clarify where needed! and thanks so much once again in advance for all your help on all my questions!!
DMS-Cerebro---SSB---Recon.mdb
0
Comment
Question by:developingprogrammer
  • 6
  • 2
9 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 668 total points
ID: 39274050
" what is the difference database model and database schema?"

I don't know about current teaching practice but when I was a lad (!!) a database model was a conceptual design and a database schema was the design as it has been or would be implemented.  A simple example is the idea of a many to many relationship.  This can exist in a data model but (in most cases) cannot exist in a physical database as it requires the introduction of a junction table.  
More generally , a data model doesn't really care about how you are going to implement the design -it is dbms-independent and doesn't even involve tables, just data.  A schema is totally dbms dependent and might different from database to database (Access versus SQL Server for example)
0
 

Author Comment

by:developingprogrammer
ID: 39274088
Great answer to database modelling and database schema! Thanks peter57r!! = )
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1332 total points
ID: 39274335
Before we could go into too much detail regarding these things, you'd have to give us an idea of what your database is intended to do, and please use concrete, real-world terms. "Use Case" scenarios would help us to understand more about the way it's intended to store data.

For example:

Give us some examples of an "Activity Code", and tell us why that's different from a "Code".

What is an Activity Code Task? How does Code Task (and Task) fit into all of this?

Why is CodeTask related to both ACtivityCodeTask and Code?

Why is Customer Account broken out into Customer? Can you have multiple Customers on a Customer Account?

As peter mentioned in your other question, it's nearly impossible to give guidance by looking only at the database structure. We have to have a real-world context in which to frame that guidance.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:developingprogrammer
ID: 39283700
hang on guys!! i think i got it!! here's my new relationship model haha = ) whaddayall think? = ))

relationship
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1332 total points
ID: 39283938
That's a nice screenshot but - again - without knowing what your database is intended to store it's impossible for us to speculate on whether it's designed properly.

One confusing aspect:

What is perfCode, and how are perfCodeDefinition and perfCodeTask related to perfCode? What is their purpose? Obviously a Code is related to a Container, but can you have a single Code with multiple CodeDefinitions - and if so, are you certain that the Code should be related to the Container, and not the CodeDefinition?

Same concept with CodeTask - apparently a Code can be related to multiple CodeTasks. Are you sure that a Code should be related to a Container, and not a CodeTask?
0
 

Author Comment

by:developingprogrammer
ID: 39286533
yup yup okie so

1) a code defines an activity of work a staff does. this code typically has 3 categories - cat 1, cat 2 and cat 3. in each category they can have an acronym. for example H:ACT-E stands for housing, account, enquiry. the colon and the dash are just delimiters

each code has a minutes of work awarded to it and also when the process was last reviewed to make sure it is optimised.

2) each code has a container. a container can only have 1 code. and a container can have a super container, and that container can have another super container etc.

3) each code also has tasks which usually must all be completed for the code to be awarded to the staff.
0
 

Author Comment

by:developingprogrammer
ID: 39289779
ok i've got an updated screenshot! = )relationship
0
 

Author Comment

by:developingprogrammer
ID: 39304390
ok guys, i think i've more or less got it nailed. the database schema and also the whole organisation of code. here's the schema and the database = )
schemaDMS-Cerebro---SSB-Recon.mdb
0
 

Author Comment

by:developingprogrammer
ID: 39343721
guys thanks for all your help so far!! = ))
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question