Northwind

[Mike]

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

 

[Rose]

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?

 

[Mike]

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]

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?