Solved

data modelling best practices

Posted on 2013-06-24
9
387 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 167 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 333 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 333 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

630 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