Re: [PHP-DB] 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 bo
th
> 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@ma
c.com...
>  
> 

-- 
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>>>>