Re: [PHP-DB] novice on table design

2005-05-17 Thread thisisroot
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 [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

EmployeeCustomernon-Customer

Address

=======
pkey pkeypkey
pkey
number type type
...
payrate grantcapital

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 

Re: [PHP-DB] novice on table design

2005-05-16 Thread Jeremiah Fisher
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 [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
   EmployeeCustomernon-Customer
Address
=======
   pkey pkeypkey
pkey
   number type type
...
   payrate grantcapital
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


Re: [PHP-DB] novice on table design

2005-05-14 Thread Tony S. Wu
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
EmployeeCustomernon-Customer
Address
=======
pkey pkeypkey
pkey
number type type
...
payrate grantcapital
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


RE: [PHP-DB] novice on table design

2005-05-13 Thread Miguel Guirao
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

EmployeeCustomernon-CustomerAddress
=======
pkey pkeypkey
pkey
number type type
...
payrate grantcapital

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



Re: [PHP-DB] novice on table design

2005-05-13 Thread tony yau
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

 EmployeeCustomernon-Customer
Address
 =======
 pkey pkeypkey
 pkey
 number type type
 ...
 payrate grantcapital

 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