Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

data modelling best practices

Posted on 2013-06-24
9
Medium Priority
?
396 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 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
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 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

715 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