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" <tonyswu@xxxxxxx> wrote in message
news:c294d647ad3c9be9aa3c37b9ccf98df4@xxxxxxxxxx
> 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
> tonyswu@xxxxxxx
>
>
>
> 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" <miguel.guirao@xxxxxxxxxxxxxxx> wrote in message
> > news:GFEHIFBDMNHCPDFHEJDGEEOOCDAA.miguel.guirao@xxxxxxxxxxxxxxxxxx
> >> 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:tony.yau@xxxxxxxxxxxx]
> >> Sent: Viernes, 13 de Mayo de 2005 09:27 a.m.
> >> To: php-db@xxxxxxxxxxxxx
> >> Subject: 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
