Tables and Normalizing Data

[PS]

Hello,
I have a bit of dilemma in septting up my tables. I am designing a projects
database. My issue is just getting the data normalized. For each project we
have a Client and an End User (who the Purchaser may be buying the equipment
for, if it is not for their direct use).
Many of our Clients and End Users have several locations. For example, our
client could be US Dairy in Minnesota who is buying equipment to be installed
at Joe's Dairy Farm in Indiana and on another project it could be US Dairy in
Wisconson purchasing for Tucker's Farm in New York, or Joe's Dairy Farm in
Maryland and, on yet another project, it could be US Dairy in Minnesota
purchasing for US Dairy in Minnesota.
There is so much room for overlapping and I am uncertain as to how to end up
with just one entry for US Dairy, that is used everywhere.
If anyone can help me to going, I'd appreciate it.
Thanks.

 

[Jeff Boyce]

Shut off the computer and take up pencil & paper.

What are the "things" about which you will need to keep information?
"Things" could be people, places, concepts, ...

If you are tracking projects, I'm guessing you need a [Project] table.
(guessing because you know your situation best...)

You mentioned "Purchaser" ... is that one person or could several folks
serve in that role? And are "Client" and "EndUser" actual
individuals/businesses, or, as I suspect, different "roles" that those
persons/orgs could play in different "projects". Perhaps having a way to
track entities (persons, businesses, agencies, ...) leads to an [Entity]
table, and the various roles leads to a [Role] table.

If that's the case, you'd probably also need a junction table to show which
role which entity was playing ... and did you need to track that to a
specific project? Or to a specific sub-task in a specific project?

When you're done jotting down the "things" about which you need to keep
information, draw some lines between the "things" that are connected, and
note the "relationships" (can one [Project] have one-and-only-one Purchaser
.... can one Purchase be associated with multiple projects?)

After you get these, start adding facts you'll be keeping (e.g., date the
project begins, ...), showing them with the "things" they are most likely
associated with.

Now you're (mostly) ready to turn the PC back on and start designing
tables!<g>

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP


--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dorian]

It all depends on what your needs are for reporting on the data from the
database.
It seems you have Client and ClientLocation related one to many.
And possibly EndUser and EndUserLocation related one to many.
Then you have a Project table.
Does a project have a single client and end user or could it have many?
If just one, it seems project should be linked to ClientLocation and
EndUserLocation.
You can roll up the Client and EndUser records whenever you need to for
display or reporting by running appropriate queries.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Philip Herlihy]

For me, the key question is whether you should be storing Clients and End
Users in the same table - are they equivalent "entities" (despite fulfilling
different roles)? My feeling is that they are likely to be equivalent.
However, I think you'll also need to store a "location" field, as US Dairy
headquarters in Minnesota might buy a doodah for their research lab in
Vermont, and Joe may have several dairy farms. Both Client and End User
might well be best represented as (1) a legal corporate identity (USD, or
Joe) and a location/department/what-you-will. So, you'd have a table of
Partners (or some such name) with a one-to-many relationship with a table of
Locations . (For "Location" substitute whatever actually distinguishes
different branches of a business's enterprise.) Then your Projects table
would include foreign keys for Partner-and-Location twice - once for Client
and once for End User. If more than one Partner could share a Location
(could be different parts of a business ordering as different clients, or
loosely-related businesses sharing office space) then you'd need to
represent Partner-Location as a many-to-many, which would call for an
"associative" table linking keys for Partners and Locations, rather than
plugging the Partner foreign-key into the Location table.

Of course, you may get an order from one Client for two or more End Users as
a single Project, in which case you should have a separate table linking
each Project with one or more End Users / Location combinations.

My heuristic for this sort of stuff is:
1) Figure out what the entities are - are X and Y really the same, or
different (e.g. Client and End User: same "sort of thing"?)
2) Spot the One-to-Many relationships
3) Wonder if some of those are actually Many-to-Many relationships.
4) Check that all fields in all tables store values which are fixed by the
key (and only the key).

Thinking as I write (that should set the alarm bells ringing!) I'd ask what
is it you need to store about the End User? Is it the shipping location, or
maybe some intricacy over state taxes? Might make a difference.

Phil, London