(I guess this means it's example-counterexample time....)
Business Rule 1: Each store can have up to three managers
Business Rule 2: No manager can run more than one store.
Q1: what kind of relationship is this
a. one to one
b. one to many
c. many to many?
(answer at the end of the lesson)
Here are two ways to model the relationship between mangers and stores.
Method 1:
CREATE TABLE store (
ID int auto_increment,
Name varchar(40),
Manager_ID1 int,
Manager_ID2 int,
Manager_ID3 int
)
CREATE TABLE manager (
ID int auto_increment,
Name varchar(40)
)
Method 2:
CREATE TABLE store (
ID int auto_increment,
Name varchar(40)
)
CREATE TABLE manager (
ID int auto_increment,
Name varchar(40),
Store_ID int
)
Both methods meet the business requirements. Method 1 seems to have an
advantage in that it enforces rule #1. However, managing data using method
1 is much harder.
Q2: What query would you need to write to figure out which store is
managed by "Joe Smith"?
A2: (one method, there are many others)
SELECT @ID = id from manager where name='Joe Smith'
SELECT s.name
FROM store s
WHERE [EMAIL PROTECTED] or [EMAIL PROTECTED] or [EMAIL PROTECTED]
Q3: Manager "Mary" moves to a new store. Write the statement that updates
a store record so that her name ends up in an available manger_ID# field
(but only once)
A3: (I don't even want to try. Trust me, it's ugly)
What happens if you need to hire a 4th manager? The database must be
changed. Each and every statement in all parts of your code that deals
with stores or managers will need to be changed to recognize and deal with
the new field (manager_id4). What seemed like a good design actually
creates a maintenance nightmare. Everything you do, you wind up doing in
threes (checking 3 fields for matching values, making 3 joins from store
to manager.) You tripled ( or 9x-ed) your workload for no good reason.
Let's examine Method 2. This model requires your application to enforce
the quotas of your business rules but your data structure does not
need to change every time someone creates a change in the external
environment. Moving mangers from store to store takes only a simple update
A3:
UPDATE manager
SET Store_ID = 15
WHERE name = 'Mary'
Q4: How do you add another manager to a store?
A4: You set another manger's Store_ID to the ID value of the store.
Q5: How do you find the store managed by "Joe Smith"
A5:
SELECT s.name, m.name
FROM store s
INNER JOIN manager m
on m.store_id = s.id
WHERE m.name = 'Joe Smith';
Q6: How do you find the managers for the store "Maple Street Books"?
A6: (we can reuse most of the same query we wrote for A5)
SELECT s.name, m.name
FROM store s
INNER JOIN manager m
on m.store_id = s.id
WHERE s.name = 'Maple Street Books';
Make any sense? BTW - were you able to answer the first question? A1: b.
To create your M:N scenario, just extend my example by imagining a
"personnel" table and mentally change the manager table so that it
contains only two fields "personnel_id" and "store_id". Voila! you are now
into a many to many design. Really, it's only two one-to-many
relationships (one from personnel to manager, the other from store to
manager). Tracing through the relationships is no more difficult than in
the
one-to-many design. In many cases, it only requires one additional JOIN to
make your query work.
It's (almost) always better to enforce quota-based business rules in your
application rather than the database (nothing is perfect for EVERY
situation). Not only will it simplify your SQL statements but your future
maintenance (data maintenance, database maintenance, and code maintenance)
will also be greatly simplified.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Stuart Felenstein <[EMAIL PROTECTED]> wrote on 10/05/2004 06:52:19 AM:
> Great example and my apologies because as you know
> we've been down this road before.
>
> Side note - once again I did not provide my acutal
> table names , this time they haven't been created as
> of yet.
>
> Let me skinny this down though a bit and focus on one
> aspect of my design connundrum.
>
> Remember I am designing for a "job board". Users can
> choose say up to 3 locations where they would like to
> look for work. So if my thinking is correct , this is
> a possible schema:
>
> Users_table * Location_table * Users_Location_table
> (the - MTM)
>
> Now I think the above is right, but I ask myself, what
> are the real drawbacks if I do something like this:
>
> Location_table:
> indentifying fields .(userID's, recordID's)..
> Location1
> Location2
> Location3
>
> This is the point I know that is sticking me -
> understanding why the first example is better then the
> second.
>
> Thank you ,
> Stuart
>
> --- [EMAIL PROTECTED] wrote:
>
> > Tables are tied together by whichever field(s) you
> > use to store their
> > "parent"'s reference.
> >
> > For one second, imagine I am writing an inventory
> > control program for
> > somebody like Wal-Mart or Target. Those businesses
> > have so many locations
> > that they are divided into regions, each region will
> > have multiple
> > warehouses, each region would also have multiple
> > stores. Each store could
> > be within supply range of several warehouses. Each
> > warehouse can supply
> > several stores.
> >
> .................
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
>
>
