Solved

data modelling best practices

Posted on 2013-06-24
9
328 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 167 total points
Comment Utility
" 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
Comment Utility
Great answer to database modelling and database schema! Thanks peter57r!! = )
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 333 total points
Comment Utility
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
 

Author Comment

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

relationship
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 333 total points
Comment Utility
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
Comment Utility
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
Comment Utility
ok i've got an updated screenshot! = )relationship
0
 

Author Comment

by:developingprogrammer
Comment Utility
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
Comment Utility
guys thanks for all your help so far!! = ))
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now