Hi Tony, Miguel

    yes that was my intention at first, but to absorb all three, Shop,
Employee, and Customer (and there may be 2 more to come) into an Address
table would be inefficient both in storage space and search time,..no?

having this compound keys at a separate Address table is essentially the
same idea, but I know it doesn't 'feel' right, for a start in Visio I can't
put a link to the Address table (because fkey can't be a foreign key to both
Shop and Employee)!!!

Apart from that, the tables are efficient, searching would be much quicker
for non-address info.

Tony

"Tony S. Wu" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> actually, no, Shop, Employee, and Customer are not distinct.
> in your instance they are the same type of entry.
> don't distinguish them by tables, rather use a column to hold some sort
> of an ID for each type.
> of course you'll end up with a table with many columns, and many of
> them will be null depending on which type an entry is.
> but with this approach, you can easily associate with an address table.
>
> Tony S. Wu
> [EMAIL PROTECTED]
>
>
>
> On May 14, 2005, at 4:49 AM, tony yau wrote:
>
> > Hi Miguel,
> >     Thanks for the reply.
> >
> >     the non-customer is actually a Shop, so Employee, Customer and
> > Shop are
> > distinct enough to have their own tables. Now they all have an
> > Address, and
> > the problem is how do I allow multiple addresses for each these
> > 'people'
> > (without using
> > a lookup table)
> >
> > tony.
> >
> > "Miguel Guirao" <[EMAIL PROTECTED]> wrote in message
> > news:[EMAIL PROTECTED]
> >> The schema of your table is wrong, is you do bnormalize it you will
> >> find
> > out
> >> that you need two tables for this approach.
> >>
> >> One table for your people and another one for the n addresses of your
> >> people.
> >>
> >> If you keep your current schema, you will have as many rows for one
> >> person
> >> as many addresses for that person you have, and you will be
> >> duplicating
> > many
> >> fields. So you must split your tables, one for your people and
> >> another for
> >> your people's addresses.
> >>
> >> -----Original Message-----
> >> From: tony yau [mailto:[EMAIL PROTECTED]
> >> Sent: Viernes, 13 de Mayo de 2005 09:27 a.m.
> >> To: php-db@lists.php.net
> >> Subject: [PHP-DB] novice on table design
> >>
> >>
> >>
> >> Hi all,
> >>
> >> I have the following tables
> >>
> >>     Employee            Customer            non-Customer
> > Address
> >> ==========    ==========    =============    ==========
> >>     pkey                     pkey                    pkey
> >> pkey
> >>     number                 type                     type
> >> ...
> >>     payrate                 grant                    capital
> >>
> >> I need to allow the three types of people to have n addresses, so I've
> > added
> >> a type to distinguish the 3 types of people and their respective pkey
> >> onto
> >> address table.
> >>
> >>     Address
> >> =========
> >>     pkey
> >>     ...
> >>     type    (either Employee, Customer or non-Customer etc)
> >>     fkey    (the pkey of Employee, Customer or non-Customer etc)
> >>
> >> I know this design looks awkward but it does have the advantage of
> >> having
> >> less tables otherwise.
> >> BUT somehow it doesn't feel right. Can someone points me its pros and
> > cons.
> >>
> >> thanks all.
> >> Tony Yau
> >>
> >> --
> >> PHP Database Mailing List (http://www.php.net/)
> >> To unsubscribe, visit: http://www.php.net/unsub.php
> >
> > -- 
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
>


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to