Solved

data modelling best practices

Posted on 2013-06-24
9
354 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
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 84

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 84

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 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