Re: novice on table design

Addresses should definitely have their own table. Have you considered using Postgres? It allows for table inheritance in much the same way that inheritance works in OOP. You could have a parent table (like a parent class) for "People", and child tables (just like child classes) for "Employee", "non-Customer", and "Customer". This is the preferred approach, but few other RDMBS solutions support this (or much SQL 99 at all). If you're not using Postgres, another way to do this would be to create a many-to-many relationship between your "People" tables, so that an address ID is related to a person ID. Visio balks at using a non-unique foreign key because this is bad design. A "key" must be unique by nature, and indexing is much faster the column is guaranteed to have unique values. If you go with your original approach, make a combined key on both the type and foreign key fields. Hope this helps, Jeremy Tony Yau wrote: > 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@mac.com> wrote in message > news:c294d647ad3c9be9aa3c37b9ccf98df4@mac.com... > >>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@mac.com >> >> >> >>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@mail.telcel.com> wrote in message >>>news:GFEHIFBDMNHCPDFHEJDGEEOOCDAA.miguel.guirao@mail.telcel.com... >>> >>>>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@emigen.co.uk] >>>>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 ********************************************************************** IMPORTANT NOTICE This communication is for the exclusive use of the intended recipient(s) named above. If you receive this communication in error, you should notify the sender by e-mail or by telephone (+44) 191 224 4461, delete it and destroy any copies of it. This communication may contain confidential information and material protected by copyright, design right or other intellectual property rights which are and shall remain the property of Piranha Studios Limited. Any form of distribution, copying or other unauthorised use of this communication or the information in it is strictly prohibited. Piranha Studios Limited asserts its rights in this communication and the information in it and reserves the right to take action against anyone who misuses it or the information in it. Piranha Studios Limited cannot accept any liability sustained as a result of software viruses and would recommend that you carry out your own virus checks before opening any attachment. ************************************************************************ <<<<GWAVAsig>>>>

AdmID:7A70616722DEA177A8D6BC99B0CB98E2 ********************************************************************** IMPORTANT NOTICE This communication is for the exclusive use of the intended recipient(s) named above. If you receive this communication in error, you should notify the sender by e-mail or by telephone (+44) 191 224 4461, delete it and destroy any copies of it. This communication may contain confidential information and material protected by copyright, design right or other intellectual property rights which are and shall remain the property of Piranha Studios Limited. Any form of distribution, copying or other unauthorised use of this communication or the information in it is strictly prohibited. Piranha Studios Limited asserts its rights in this communication and the information in it and reserves the right to take action against anyone who misuses it or the information in it. Piranha Studios Limited cannot accept any liability sustained as a result of software viruses and would recommend that you carry out your own virus checks before opening any attachment. ************************************************************************ <<<<GWAVAsig>>>>