D
dumbo
What should i ask myself before creating a table?? what
are the basic thinga i need to know before creating a
table?
are the basic thinga i need to know before creating a
table?
V
Van T. Dinh
If you know nothing about Relational Database Design
Theory and never heard of Database Normalization, read a
book on Relational Database Design Theory before touching
Access software.
It is a theoretical step but without the knowledge, it is
virtually impossible to develop a well-structured and
efficient database application!
HTH
Van T. Dinh
MVP (Access)
Theory and never heard of Database Normalization, read a
book on Relational Database Design Theory before touching
Access software.
It is a theoretical step but without the knowledge, it is
virtually impossible to develop a well-structured and
efficient database application!
HTH
Van T. Dinh
MVP (Access)
D
Dirk Goldgar
dumbo said:
I'm going to disagree with Van for once on this question. If you were
planning to develop a professional database application, then I'd say
yes, you really must learn about relational database design before
plunging in. But if you are looking to develop a simple, workable
application for yourself and maybe a few other forgiving people, then
you can go a long way if you just think up front about a few simple
questions and principles:
1. What are the real-world objects, entities, and concepts I'm trying to
represent? Each distinct type of entity should have a table to
represent it, wherein each record represents one entity of that type.
For example, customers, contracts, books, authors, etc., are all
"entities". Even very abstract concepts may be entities, if that's the
sort of thing you need to represent.
2. For each type of entity, what adjectives and attributes would be
appropriate to describe it? These will generally be fields in the table
representing that type of entity.
3. For each entity, is there some unique attribute that distinguishes
this entity from all other entities of that type? This will be the
"primary key" of the table. For example, a customer number, an ISBN, a
serial number, even a name (but watch out for these -- they're often not
unique). If there is no natural key to use for this purpose, you'll
usually want to let Access create one for you: an "autonumber".
4. How are these entities related to each other? Does one entity "own"
one or more entities of a some other type? In the table designs, such
relationships are represented by having key fields in common. So a
Contracts table might have a CustomerID field in it, which indicates
that this contract is with the customer (in the Customers table) that
has that CustomerID.
4a. Sometimes you need a special table just to represent the
relationship between two other tables. This is normally the case with
many-to-many relationships. For example, a book may have multiple
authors, and each author may have written multiple books. So if you had
a table of Books with primary key BookID, and a table of Authors with
primary key AuthorID, you would need another table, BooksAuthors, with
records consisting of the fields BookID and AuthorID, to represent the
possible relationships between books and authors.
In addition to the above questions to think about in planning your
design, here are some principles to adhere to:
A. Don't try to store two pieces of information in one field.
B. Don't store data in field names. For example, this is a bad list of
fields for a table of Sales data: JanuarySales, FebruarySales,
MarchSales, ... DecemberSales. Better would be this list: SalesMonth,
SalesAmount. Don't worry, queries can pull out the monthly data and
spread it across the page when you need to see it that way.
C. Don't try to create one "master" table that contains all the data you
need to store -- unless you really only have one entity represented in
your database. Instead, create multiple tables, related by common key
fields, and use queries to join them when you need to bring the data
together. For data entry and editing, use forms with subforms to
display the data from related tables in a logical manner.
D. If you see multiple fields repeating themselves in record after
record in a table, it's likely that the table is actually trying to
represent two (or more) distinct entities, and it really should be
broken into multiple, related tables.
E. Except for the key fields needed to link related tables together,
don't store the same information in more than one place.
F. Don't store data that can be recalculated at any time. Calculation
is cheap, storage is expensive. Remember that queries and calculated
controls on forms and reports are available to do your calculations for
you.
V
Van T. Dinh
You have the advantage of being able to type a longer post than I can (being
more or less a 2-finger typist)...
However, being able to distinguish entities ("buy" vs "sell", concrete
entities vs abstract entities) and being able to decide whether an item of
data is atomic or not (akas Rebecca Riordan's address example) is an art in
itself.
I think for new database designers, a bit of background knowledge will give
them more confidence to create Tables as required rather than being scared
of too many Tables and (therefore) too complex. After all, Rebecca Riordan
covers the theory in about 90 sparsely-printed pages which I think reading
it is a small price to pay to get a good understanding and knowledge to
design well-structured databases.
I am sure you probably have read 100 times that number of pages about Access
....
(OK, I am biased. For some reason, I had actually learnt Database Theory
before I touched any RDBMS software.)
more or less a 2-finger typist)...
However, being able to distinguish entities ("buy" vs "sell", concrete
entities vs abstract entities) and being able to decide whether an item of
data is atomic or not (akas Rebecca Riordan's address example) is an art in
itself.
I think for new database designers, a bit of background knowledge will give
them more confidence to create Tables as required rather than being scared
of too many Tables and (therefore) too complex. After all, Rebecca Riordan
covers the theory in about 90 sparsely-printed pages which I think reading
it is a small price to pay to get a good understanding and knowledge to
design well-structured databases.
I am sure you probably have read 100 times that number of pages about Access
....
(OK, I am biased. For some reason, I had actually learnt Database Theory
before I touched any RDBMS software.)
D
Dirk Goldgar
Van T. Dinh said:
But I despair of ever reaching Tom Ellison's standard. said:
Yes, it is an art. But I'd argue that many people have a gift for art
that is improved by exercising it, even without taking art classes.
I should have included that in my "principles": Don't be afraid of
having too many tables, as long as each table represents a distinct type
of entity.
Oh, I wouldn't disagree at all. But if a person has an analytical turn
of mind and grasps the fundamental concepts, I don't think they have to
read a book before dipping a toe in the water.
Maybe, but I hadn't when I started.
Me, too, in fact. Many years ago, I ran across Date's book on
relational database theory and read it out of pure curiosity. When I
first encountered Access, my reaction was, "Wow! It's just like in the
book!"
V
Van T. Dinh
Yes, but artists learning how to draw portraits or
profiles of people often study the skeletal and muscular
structure just so that they express what they see
correctly and realistically.
OK but where do people often get the fundamental
concepts?
Also, I think that a person often develops strong
analytical skills if he/she has deep understanding of the
topic involved.
Aha ... Is this a case of "Do what I tell you and not do
what I do (have done)"? <smile>
(2 fingers have been working very hard)
Cheers
Van
D
Dirk Goldgar
Van T. Dinh said:
From newsgroup messages? ;-)
Okay, you got me. But it wasn't the details of the book that came back
to me, nor could I tell you today (without looking it up) the
differences between the various normal forms. Just knowing the basic
ideas is enough, if you think that way.
So you're going to rest them and stay out of the newsgroups for a while?
I doubt that!
V
Van T. Dinh
OK. Maybe different people have different learning styles. I prefer theDirk Goldgar said:
solid grounding of the basics before I move on to other stuff. I just don't
feel that newsgroup posts can adequately cover basic concepts like entities,
attributes, domains, relationships, etc...
True. But you had the solid understanding - from reading the (Date's)
book - my original suggestion to the O.P. <smile>, you don't need the formal
details. (What's the Fifth Normal Form?) - Improved analytical skills on
database structure follows also.
I may need to stay out of the newsgroups for a short while and do some moreSo you're going to rest them and stay out of the newsgroups for a while?
I doubt that!
paid work to pay some big expenses coming up.
Cheers
Van
D
Dirk Goldgar
Van T. Dinh said:
I know what you mean. I wouldn't have been so active yesterday and
today if I weren't stuck on something in the "real" -- a.k.a. paying --
world.
V
Van T. Dinh
My problem is that I have more than enough paid work but sometimes, I skip
paid work, laze around and get on to answering newsgroups. Then I feel
guilty afters that I should have done paid work & earned the money.
Cheers
Van
paid work, laze around and get on to answering newsgroups. Then I feel
guilty afters that I should have done paid work & earned the money.
Cheers
Van