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: email@example.com > >> 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