Re: Northwind
From: Rose (anonymous_at_discussions.microsoft.com)
Date: 05/21/04
- Next message: Vernon: "Hyperlink to Shared Folder"
- Previous message: WaltS [MVP WMC]: "Re: Make a field lookup dependent on the value in another field of a record?"
- In reply to: Mike: "Re: Northwind"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 21 May 2004 07:03:27 -0700
First, it might be easier to use MS Project for at least
part of what you're doing. Project makes tracking tasks,
the people working on those tasks, and how much time and
money it's costing you very easy to do.
But back to the example:
If you find a many-to-many, it means there is a table
in-between that needs to be created. For instance, to use
the sales example again, you may think that you're going to
be selling the same products to repeat customers, so
where's the one side of a relationship? The answer is a
sales invoice, which tracks each individual (read: unique)
instance of a customer buying stuff. The sales invoice is
in a 1-M relationship with both customers and products.
To start with your circumstance, organizations. How much
information do you need to capture just about
organizations? If it's just a name, but no address or phone
or head or anything like that, it can just be a field in
your boards table. Otherwise, it'll be its own table, and
will relate to boards when you include the org's primary
key in the boards tables.
Unique employees (primary key) - can they belong to more
than one board? If so, you can either do multiple yes/no
fields in Employees, or have a 3rd table that does nothing
but track what employees are on what board and use an AN
for your PM. (This would be akin to the line items on a
sales invoice - you could have multiple lines, meaning the
invoice number could repeat, as could the product, but
you've got an AN being the PM.) A similar table will
probably do for your costs - each cost is assigned to a
board or project or whatever, and the table is just a list
of costs. Probably your task assignments will work out
similarly.
So have your first tables be all your unique stuff -
employees, boards, tasks and skills perhaps, etc. Then
think of whether a straight relationship will work between
them, or if you need a table like the above to bring their
information together in 1 place to track it.
The line items on a sales invoice is the easiest example of
that. A line item table will capture the invoice number,
the product being sold and its costs, plus the quantity of
that product being ordered and its final line item cost
(quantity X price). Since absolutely everything can repeat,
an AN is required for a PM. It's usually displayed as a
subform in the invoice main form, so you can see both
tables at the same time, but they *are* 2 separate tables.
Does that make sense?
>-----Original Message-----
>Rose:
>
>Thank you so much for your detailed response. Having
read what you
>suggested, I doubt that modifying the Northwind db is
actually the best way
>what I'm trying to achieve.
>
>Essentially, here's the problem I'm having... I have
multiple tables that
>are all closely related to one another (at least as far as
I'm concerned).
>The concept of Northwind is really easily to follow. I
mean it's very
>obvious to follow that Suppliers have Products are product
are linked to
>Orders which in turn are placed by Customers.
>
>However, using a concept that involves the following
tables is more
>difficult to understand:
>- Boards
>- Budget Categories
>- Concepts
>- Employees
>- Organizations
>- Projects
>- Skills
>- Tasks
>
>Thinking of these tables, I could see multiple
Many-To-Many relationships
>amongst all of these tables.
>
>For instance, the following might help get better
clarification on my
>thinking pattern.
>
>Boards:
>1. Boards are subordinate to an Organization:
>2. Boards have multile Employees
>3. Running Boards will end up costing money, so there will
be funds
>allocated via Budget Categories for each board
>4. Board (well the members) will make decisions that
result into tasking
>people w/ something. This "something" might lead into
Projects
>
>Budget Categories:
>1. Each budget category is - more than likely - part of
multiple project
>2. Budget Categories are utilized by various Organizations
(actually
>Sub-Organizations)
>
>Concepts:
>1. Concept might turn into Projects and then into Tasks
>2. They have to be funded by multiple Budget Categories
>3. They will be supported by multiple Employees;
therefore, multiple skills
>are need to achieve a successful concept implementation
>
>Employees:
>1. Many employees are part of many organizations (or
boards, or projects)
>2. Many employees need many skills
>3. Many employees have many tasks
>
>Organizations:
>1. will have many projects, boards, employees, tasks,
budget categories,
>etc.
>
>Projects:
>1. I'm repeating myself here... you get the drift ...
>
>Skills:
>1. They are part of all (many) employee's backgrounds --
at least I hope so
>8)
>2. Many skills are required to running many projects,
support many boards
>3. Many skills will have to be subsidized by many budget
categories (the
>more you know the more funds may have to be allocated from
different
>sources)
>
>Finally, Tasks:
>1. Many tasks are completed by many employees
>2. They all have to be funded by many budget categories
>3. They are part of many organizations
>
>Wow, as you might realize, this is quite different than
"Northwind", isn't
>it? Looking at all of the "relationships" I'm not sure
if I have ended up
>with a challenging project here. For me, it's seems very
difficult to get
>a handle on this an create relationships that would make
sense (in a most
>efficient database design)
>
>I'm sure you're busy but if there's any way you could
provide me more
>feedback on this, I truly would appreciate it.
>
>Thanks in advance,
>Mike
>
>
>
>
>"Rose" <anonymous@discussions.microsoft.com> wrote in message
>news:ff5c01c43ebb$c6adec70$a501280a@phx.gbl...
>> If Northwind is close enough to what you want, then that's
>> fine. You can make a copy of the database (so as to keep
>> Northwind handy as is for a reference), and then make
>> changes to the copy.
>>
>> One-to-many relationships is what you want to have 99% of
>> the time, with the rest of them being 1-to-1s.
>> Your project will have workers. Those workers will have
>> costs (I'm presuming?), skills, plus possibly a host of
>> other data you want to capture about them.
>>
>> In your project table, your project number or ID will be
>> unique, and therefore your primary key.
>> You should also have a worker table with all the worker
>> data included. If there's only a few skills they can
>> possess, you might make a few checkboxes (Yes/No fields)
>> right in the worker table. Or you could have a combo box in
>> several different fields in the worker table, with the
>> different skills in the lookup table for each combo box.
>> The problem with either approach above is limiting how many
>> skills a worker can have by the number of fields you
>> include for that in the worker table.
>>
>> If you find yourself needing lots of skills and the
>> capacity for lots of skills per worker, you could:
>> 1) Separate the skills out into their own table, using the
>> employee ID for the primary key and creating yes/no fields
>> for each skill. (This is a time when a one-to-one
>> relationship is a good thing - when the sheer number of
>> fields for one record can be ginormous.) Or,
>> 2) Make a skills table. Modify the sales invoice to have it
>> pull info from the project table, the worker table, and the
>> skills table. This would make one form that would give you
>> all the information on a project, including workers and
>> skills. (Your project info would be the body of the
>> "invoice", the different workers the subform of "line
>> items", and the skills another subform of "line items"
>> you'd have to add.) This means populating a new table with
>> info from the others to associate all this info together,
>> though, so you may not want to do that. (You *would* if
>> this was really a sales invoice, so you could later go back
>> and see what was sold on each invoice.)
>>
>> That's probably enough to hit you with at once.
>>
>> >-----Original Message-----
>> >I want to modify the Northwind db example.
>> >
>> >Looking at the design, I want to include another 3 or 4
>> tables that follow the schema of the "Order Details".
>> >
>> >If I look at the relationship between "Orders" and "Order
>> Details" I think I want to be able to create multiple 1:M
>> tables.
>> >
>> >For instance, I may have a "Project" that has multiple
>> "workers", "multiple costs", "multiple skill requirements".
>> >
>> >All of these "multiples" I want to add in datasheet view
>> for the particular project.
>> >
>> >
>> >Any suggestions how modity the Northwind tables and
>> relationships to accomodate this?
>> >
>> >
>> >Thanks in advance,
>> >Mike
>> >
>> >
>
>
>.
>
- Next message: Vernon: "Hyperlink to Shared Folder"
- Previous message: WaltS [MVP WMC]: "Re: Make a field lookup dependent on the value in another field of a record?"
- In reply to: Mike: "Re: Northwind"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
