Groups

[Marc]

I’m having trouble on how to design the tables for my database. Currently I
have 10 locations and they report their revenue each month by classification.
There are 10 different classifications. Now the classifications are also
classified into 5 groups and the classifications can be in one or more
groups. I would like to establish the groups and just have data fall into
the rightful group the classification is in. Not sure how to accomplish this.
Any ideas?

 

[scubadiver]

Does this help?

Locations -> Revenue <- Groups <- Classification

" -> " means "1-to-many" in that direction

 

[scubadiver]

In fact it should be:

Locations -> Revenue <- Classification <- Groups

tble_group
GroupID (PK)

tble_Class
GroupID (FK)
ClassID (PK)

tble_Revenue
ClassID (FK)
LocationID (FK)

tble_location
LocationID (PK)

All IDs should ideally be long integer numbers.

 

[Marc]

Thank you for the response.

Say I have one classID and it falls into 3 different groups. How do I assign
one classID to all 3 groups?

 

[scubadiver]

That is what confused me slightly. So maybe it should be:

Locations -> Revenue <- Classification -> ClassGroup <- Groups

"Revenue" and "ClassGroup" are junction tables

tble_group
GroupID (PK)

tble_classgroup
GroupID (FK)
ClassID (FK)

tble_Class
ClassID (PK)

tble_Revenue
ClassID (FK)
LocationID (FK)

tble_location
LocationID (PK)

Set up these tables and go to

tools -> references.

Show all the tables and then drag the appropriate fields between the
appropriate tables then you can visualise the relationships. For each
relationship establish referential integrity.

 

[Marc]

I set up the tables and went to tools and couldn't find references. Do I need
to go somewhere else to find references?

 

[scubadiver]

I didn't say anything about references. To create the table relations

tools -> relationships

Show all the tables

then drag the primary key to the foreign key.

 

[BruceM]

I didn't say anything about references.

Actually, you did.
In this post you used "relationships", as you no doubt intended in the
previous one.

 

[scubadiver]

Just testing! LoL

 

[BruceM]

I'm having trouble on how to design the tables for my database. Currently
I
have 10 locations and they report their revenue each month by
classification.
There are 10 different classifications. Now the classifications are also
classified into 5 groups and the classifications can be in one or more
groups. I would like to establish the groups and just have data fall into
the rightful group the classification is in. Not sure how to accomplish
this.
Any ideas?

I agree with scubadiver that it sounds like you need junction tables, but
some things are unclear, at least to me. Are the ten locations subsets of
something else (are they branches of the company, or what exactly)? Are the
classifications used by all locations, or does each have its own, or both?
How do the classification groups fit into the picture? Some sample data may
help make this more understandable.

If each location can report revenue from any of ten classifications, and if
each classification can be associated with several locations, there is a
many-to-many relationship between classifications and locations, so a
junction table is needed to resolve the relationship.

If each classification can be in several groups, and each group can have
several associated classifications, again there is a many-to-many
relationship, this time between groups and classifications. Another
junction table is needed.

Once this is clearly defined, you can start considering how the user will
interact with the data. For instance, will they select a classification or
a group when reporting revenue?