Table vs. multiple tables

[jenniferspnc]

So I had a database that served it's purpose; however, there have been lots
of changes in the information captured so I think I'll need to start from
scratch so it works. I need some suggestions to ensure I can get the end
result. I'm not certain if I can capture all the information in one table or
if I will need multiple tables using a relationship.

Here's what I've started;
Acct Customer Region Month Revenue GP
Type

There are 3 regions and 3 types. So right now I was thinking each month I
would have 9 entires per customer.
In the end I'll want it to be a summary view, to say customer XYZ, for some
date range in APAC, had a total of $ in Revenue for type 2 which accounted
for % of the total revenue.

So should I keep this all in a table or should I have a separate table for
Region or Type?

Sorry if my question is elementary or not detailed enough, just don't have
that much understanding of when to use multiple tables. Thanks.

 

[George Nicholson]

Type refers to what?
Region refers to what?

My guess would be:

Customer table:
CustAcct (Primary Key?)
CustName
CustRegion
CustType

Sales table
CustAcct (Foreign Key)
SalesMonth
SalesAmount

If GP is Gross Profit, it is a calculated value and *not* usually stored,
just calculated on demand. Nothing in you post hints at where the Cost
element of this calculation would come from.

You could have separate Lookup tables for Region and Type in order to
facillitate data validation. Entries in the Customer table would be limited
to values in the Lookup tables corresponding to those fields via
relationships with Referential Integerity imposed.

There are 3 regions and 3 types. So right now I was thinking each month I
would have 9 entires per customer.

Not sure why you would need 9 for each Customer unless Region & Type refers
to Sales rather than Customers *and* all customers always purchase all types
in all regions, which would be a bit unusual.

 

[Jeff Boyce]

Tables are a way to show the interrelationship of "clumps" of data. There's
no magic way to determine "how many tables" ... relational database design
is part science, part art.

Here's an approach that works for a lot of folks ... first turn off the
computer and grab paper/pencil.

How many different "clumps" (things about which you want to keep data) can
you jot down? These are (formally) termed "entities". From your
description below, it looks like you have:

Customers
Types (?!of what?!)
Regions
?Accounts
and maybe others.

Next, what are some "facts" about each of these (and your others) that you
want to keep? Jot these down under each entity. For example, Customers
probably have names (use FirstName and LastName ... it makes sorting by
LastName much easier!). If they have only one address, phone, email, ...
(contact info), these would be facts about customers. If, in your
situation, your customers have multiple addresses, multiple phone numbers,
...., you'll need to handle that with more tables -- too complex for the
moment.

You have entities (clumps of data) and facts (formally termed "attributes")
about those entities.

Now, how are the entities inter-related? For example, I'll guess that a
customer can have multiple Accounts (one {customer} to many {accounts})...
but you know your situation, I don't. Draw some lines between pairs of
tables, showing one-to-one relationships (rare), one-to-many (quite common),
and many-to-many (you'll need to do some special handling on these).

Congratulations, you have an entity-relationship diagram! (by the way, I
have no idea how revenue is related in here, but "Month" is most likely an
attribute, not an entity)

Do a bit of research on Boyce-Codd rules of normalization (no relation, no
pun) and make sure your diagrams follow these rules.

Now turn your computer back on ... the entities become your tables, the
attributes your tables' fields, and the relationships you set using the
relationship menu/window.

How many tables ... ?! How many entities?!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jamie Collins]

Tables are a way to show the interrelationship of "clumps" of data. There's
no magic way to determine "how many tables" ... relational database design
is part science, part art.

Here's an approach that works for a lot of folks ...
<<snipped>>
You have entities (clumps of data) and facts (formally termed "attributes")
about those entities.

I appreciate you are trying to simplify things for a newbie (dumb
down, even) and you've done a good job of it (IMO) but I think it's
worth pointing out that a large part of the 'art' aspect you mentioned
is deciding (read: agonizing over) whether data elements are entities
or attributes; I frequently find myself 'converting' a previously-
considered attribute to an entity.

Now turn your computer back on ... the entities become your tables, the
attributes your tables' fields, and the relationships you set using the
relationship menu/window.

How many tables ... ?! How many entities?!

You seem to be saying that the number of tables equates with the
number of entity types (other ancillary table types notwithstanding:
lookup table, data load table, calendar table, sequences table, etc).

A fundamental point of design: a table can model either an entity or a
relationship between entities but not both.

If one is (exclusively?) using the Access Relationships window to
create relationships between entities it suggests one has attributes
that aren't really attributes. This is a frequently-encountered flaw.

Take Northwind, for example. It has entity types 'customer',
'employee' and 'order'. The relationships between entities
order:customer and order:employee should (IMO) be modelled using
distinct relationship tables. However, Northwind erroneously (IMO)
considers 'employee' and 'customer' to be attributes of an 'order',
simply because those entity relationships are 1:1 (don't believe
anyone who tries to tell you that 1:1 relationships are rare <g>). If
those relationships were 1:1..3 and the table had (nullable)
attributes 'employee_1', 'employee_2' and 'employee_3' etc then the
flaw would be more obvious (and we'd soon hear the accusations of
"thinking spreadsheet").

Jamie.

--

 

[Jeff Boyce]

Jamie

Your clarifications are on point.

Your assumption that the original poster is a "newbie" may not be accurate.

And I'm not sure how I should take the "dumb down" comment ... I described
the process I use!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pat Hartman]

I disagree totally with your assessment of the relationship between customer
and order. It is not 1:1, it is 1:m. An order is for one customer but a
customer may place many orders. A separate relation table would go even
further and imply a m:m relationship. If you think customerrder is 1:1,
no wonder you have this unnatural idea that 1:1 relationships between
entities abound in the real world.

 

[Tony Toews [MVP]]

a large part of the 'art' aspect you mentioned
is deciding (read: agonizing over) whether data elements are entities
or attributes; I frequently find myself 'converting' a previously-
considered attribute to an entity.

Whereas I don't have a clue about entities and attributes and all that
other fancy, schmancy terminology and definitions. I think in terms
of tables and relationships.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jamie Collins]

the relationship between customer
and order. It is not 1:1, it is 1:m. An order is for one customer but a
customer may place many orders. A separate relation table would go even
further and imply a m:m relationship.

Sorry you've lost me. If the relationship is 1:m (which sounds
correct) why exactly does a separate table imply a m:m relationship?

Jamie.

--

 

[Jamie Collins]

Whereas I don't have a clue about entities and attributes and all that
other fancy, schmancy terminology and definitions. I think in terms
of tables and relationships.

You are probably being flippant for comic effect but I'll make the
obvious point anyway...

Having a taxonomy is useful for a public forum. For example, when you
say "relationships" do you mean "Access Relationships", being the
'things' that one creates in the Relationships window of the Access
UI? If so, it would be helpful if you use title case for proprietary
features ("Access Relationships"). Access Relationships, however,
merely create metadata that the Access Query Builder Tool Thing (yes,
I do know its name, I'm trying to be derogatory) uses to write join
clauses,
which makes little sense in context (this being the 'tables' group).
So you probably meant "Access Relationships with referential integrity
enforced", a much more powerful feature because it creates engine
level FOREIGN KEYs (SQL keywords in uppercase, please). Then again,
your whole sentence would be, "I think in terms of tables and one
flavor of table constraint" doesn't sound very realistic so who knows?

To clarify, I was referring to logical relationships (general term in
lowercase), which are modelled using tables.

PS speaking of taxonomy, sincere thanks for "fancy schmancy" which I'd
never heard of before

Jamie.

--

 

[Pat Hartman]

Because normally, the only time you would create a "third" table to hold the
relationship is if the relationship were m:m. There are rare instances,
where you would use a junction table to implement a 1:m relationship because
of a business rule that couldn't be implemented any other way. We had a
discussion regarding one not too long ago. I don't remember the details but
it had something to do with someone wanting to implement a "cascade to null"
action with Access. Since this feature is not supported, I suggested using
a junction table which would allow the relationship to be deleted as a
result of cascading the delete of the one side row.

 

[Jamie Collins]

Perhaps you are getting me confused with someone else...

the only time you would create a "third" table to hold the
relationship is if the relationship were m:m.

That's not what I said. I said in this thread, "The relationships [in
Northwind] between entities order:customer and order:employee should
(IMO) be modelled using distinct relationship tables." I'm really
confused that you would seemingly try to tell me I do something that I
explicitly said I would not.

We had a
discussion regarding one not too long ago. I don't remember the details but
it had something to do with someone wanting to implement a "cascade to null"
action with Access. Since this feature is not supported, I suggested using
a junction table which would allow the relationship to be deleted as a
result of cascading the delete of the one side row.

I don't think it could have been with me because I rarely have
nullable columns at all and I have never used the ON DELETE SET NULL
referential action BTW this is supported in Jet 4.0 (and above) but
not ON UPDATE SET NULL despite what the Access Help says.

Jamie.

--

 

[Tony Toews [MVP]]

You are probably being flippant for comic effect but I'll make the
obvious point anyway...

Only somewhat. I have no format training in databases or computers or
software.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jamie Collins]

I don't remember the details but
it had something to do with someone wanting to implement a "cascade to null"
action with Access. Since this feature is not supported

Just to clarify: the SQL-92 standard, to which Jet 'aspires', supports
four referential actions:

CASCADE
| SET NULL
| SET DEFAULT
| NO ACTION

Jet 4.0 (and above) supports CASCADE, SET NULL and NO ACTION, though
it supports SET NULL for the delete rule only.

Please clarify what you mean by, "a 'cascade to null' action... is not
supported."

TIA.

Jamie.

--

 

[Jamie Collins]

I have no format training in databases or computers or
software.

You certainly do not require any to be able to express opinions in
this public Usenet discussion group.

Jamie.

--

 

[Pat Hartman]

Thanks. I stand corrected on that. I rarely use DDL to create or modify
table objects so I forget the things that the GUI does not support. I'm
going to add "updating the GUI to reflect current Jet options" to the wish
list I send to the Access team every year.

 

[Jamie Collins]

I rarely use DDL to create or modify
table objects so I forget the things that the GUI does not support. I'm
going to add "updating the GUI to reflect current Jet options" to the wish
list I send to the Access team every year.

I guess there will always be advanced features and settings only
accessible via code but, sure, there are glaring omissions in the
Access UI.

Your wish is a good one but a little non-specific for me. I'd request
you make a specific proposal e.g. for the exposure of Jet's CHECK
constraints in the UI, which would have a positive knock on effect for
the Query Builder Tool Thing as they'd need to expand it to be able to
generate subqueries and should also enhance our understanding of Jet
CHECK constraints (to date Microsoft have published only scant
information).

If non-specific wishes are the norm (I really don't know how these
things work, what with the MVP program being non-transparent) then
something more achievable IMHO for the Access team would be to address
the impedance between DAO and Jet (again, as an example, CHECK
constraints are not exposed via DAO).

Perhaps someone reading this with an Access-related website will be
inspired to create some handy reference lists of Jet features not
exposed in the Access UI and DAO respectively.

PS my wish would be to get more details about the features already
present in the engine e.g. the equivalent of the below article for
Jet's DECIMAL data type (this kind of stuff must be documented
somewhere, surely we have a right to know <g>?!):

Precision, Scale, and Length
http://msdn2.microsoft.com/en-us/library/aa258274(SQL.80).aspx

PPS just think of the features and settings that must exist in the Jet
engine but are not exposed at all (DAO, DDL, OLE DB provider, registry
key) by accident or design ;-)

Jamie.

--

 

[Pat Hartman]

I was just generalizing here. When I send the list, I will provide specific
examples.

The Jet Programmer's Guide used to provide a lot of useful information but
it hasn't been updated since Jet 3.5 and so is somewhat out of date. You
might want to contact the author/publisher to see if you can repubulish it
if you have any interest in writing. There is lots of new stuff to include
now that the Access team has taken over Jet (now called ACE?) from the SQL
Server team.

If you have the time and inclination to create the cross-reference, I can
get it into the hands of the Access team for review.

 

[Jamie Collins]

The Jet Programmer's Guide used to provide a lot of useful information but
it hasn't been updated since Jet 3.5 and so is somewhat out of date. You
might want to contact the author/publisher to see if you can repubulish it
if you have any interest in writing. There is lots of new stuff to include
now that the Access team has taken over Jet (now called ACE?) from the SQL
Server team.

It's a fantastic idea but the underlying problem is catch-22 in
nature: where do I get the information on which to write the
revision?

Let's take a first step and say I was interested in publishing an
article on Jet's DECIMAL data type, introduced in Jet 4.0.

Jet is a Window component 'owned' by the SQL Server team (IIRC the
Access take have merely been granted a private branch of the code).
[Aside: yes, 'ACE' is the accepted term in these groups and the
general term 'Jet' is taken to encompass ACE].

From the aforementioned SQL Server 2000 article I note, "In previous
versions of SQL Server, the default maximum was 28."From the few
details Microsoft has given us about Jet's DECIMAL data type we know
that it has a maximum precision of 28; from experience I've noted that
the default precision is often 28 e.g.

? CurrentProject.Connection.Execute("SELECT 0.5;")(0).Precision
28

So I would speculate that a good starting point would be the
equivalent SQL Server 7.0 article; a quick Google suggest such a
document did exist because I found a broken URL.

[Aside: the chapters from the Jet Database Engine Programmer's Guide,
which I believe is the book you referred to, I found on Microsoft's
site are classed as "SQL Server 7.0 Product Documentation", which may
just be a coincidence (http://www.microsoft.com/technet/prodtechnol/
sql/70/proddocs/msjet/jetch04.mspx).]

I could then test behaviour in Jet against the SQL Server 7.0
article... but I foresee problems. If I experience an inconsistency,
how do I know whether it is a bug or behaviour by design? (assuming I
was able to ascertain that the behaviour was in fact Jet rather than
another component) Surely, I first need to know what the designer's
intention was i.e. a spec?

Allen Browne once wrote of sorting a column of DECIMAL values: "Nulls
and zeros sort unpredictably - at the beginning, middle or end,
depending on the data." (http://allenbrowne.com/bug-08.html).
Microsoft have told us that Jet's NULL collation is "that null values
are sorted at the low end of the list" (http://msdn2.microsoft.com/en-
us/library/aa140022(office.10).aspx), based on the reasonable
assumption that the OLE DB provider reflects its underlying engine. I
cannot reproduce a simple sort where NULLs in a DECIMAL column sort
anywhere other than and the end of a resultset, even when using the
mdb file supplied by the author. I think if NULL collation was broken
in Jet 4.0 then it would be significant enough to warrant its own MSDN
article. I've nothing to suggest the NULL collation is broken other
than the anecdotal evidence above i.e. no description of the steps to
reproduce such a scenario.

In case you think I've gone OT let me make my point: observation alone
doesn't amount to proof. That is why IMO the onus is on Microsoft to
provide such information rather than for me to discover their designs
by trial and error.

Jamie.

--