Table vs. multiple tables

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.

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.

--
HTH,
George


"jenniferspnc" wrote in message
...
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.

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

"jenniferspnc" wrote in message
...
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.

On Dec 19, 12:09 am, "Jeff Boyce" wrote:
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.

--

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

"Jamie Collins" wrote in message
...
On Dec 19, 12:09 am, "Jeff Boyce" wrote:
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.

--

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.

"Jamie Collins" wrote in message
...
On Dec 19, 12:09 am, "Jeff Boyce" wrote:
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.

--

Jamie Collins wrote:

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/

On Dec 20, 4:24 pm, "Pat Hartman" please no wrote:
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.

--

On Dec 21, 1:01 am, "Tony Toews [MVP]" wrote:
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.

--

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" wrote in message
...
On Dec 20, 4:24 pm, "Pat Hartman" please no wrote:
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.

--