Newbie 'structure' design query

[Kenny]

I am trying to design a database to hold company names, company
contacts, individuals etc. I'm new to databases, have read until my
head hurts, but now its time to move. My gut feeling tells me to use
the same tables for both companies and individuals. I want to store
any information necessary, eg, irrespective of how many email
addresses or telephone numbers, bank accounts etc they might use, and
my attempt uses 4 tables.

Can anyone give me an opinion (however harsh) about whether the
structure shown below would meet that requirement, say for a maximum 1
million expected records. PK's not autonumbered, I will generate and
supply at Insert time.

1. Have I missed anything really important?
2. Do professionals handle this sort of requirement in a better way?
3, Does it appear that I need to learn a hell of a lot more yet and
if I am way off course, suggest any online tutorial that would benefit
me.

Many thanks in advance for anyone that can help

Ken

Proposed structure

TABLE_NAMES
NameID PK
LocationID Indexed (if Contact, points to company NameID)
IsCompany Bool
LastName Text(32) (if Iscompany, company name here)
FirstName Text(32)
Title TinyInt 1=Mr 2=Ms 3=Ltd 4=Plc 5=Edu 6=Gov etc
UserName Text
PassWord Text

TABLE_COMS
ID PK
NameID Indexed
ComType TinyInt (1=Tel 2=Mobile 3=Fax 4=Email Address etc)
ComInfo Text(255)

TABLE_ADDRESS
ID PK
NameID Indexed
Addr1 Text
Addr2 ....
.........

TABLE_FINANCE
ID PK
NameID Indexed
Bank_NameID Indexed (Banks NameID for Banks address etc)
BankName
BankSort
AccName
AccNumber
CCType
CCName
CCNumber
CCExpiry...
........

[Jerry Stuckle]

Kenny wrote:

> I am trying to design a database to hold company names, company
> contacts, individuals etc. I'm new to databases, have read until my
> head hurts, but now its time to move. My gut feeling tells me to use
> the same tables for both companies and individuals. I want to store
> any information necessary, eg, irrespective of how many email
> addresses or telephone numbers, bank accounts etc they might use, and
> my attempt uses 4 tables.
>
> Can anyone give me an opinion (however harsh) about whether the
> structure shown below would meet that requirement, say for a maximum 1
> million expected records. PK's not autonumbered, I will generate and
> supply at Insert time.
>
> 1. Have I missed anything really important?
> 2. Do professionals handle this sort of requirement in a better way?
> 3, Does it appear that I need to learn a hell of a lot more yet and
> if I am way off course, suggest any online tutorial that would benefit
> me.
>
> Many thanks in advance for anyone that can help
>
> Ken
>
> Proposed structure
>
> TABLE_NAMES
> NameID PK
> LocationID Indexed (if Contact, points to company NameID)
> IsCompany Bool
> LastName Text(32) (if Iscompany, company name here)
> FirstName Text(32)
> Title TinyInt 1=Mr 2=Ms 3=Ltd 4=Plc 5=Edu 6=Gov etc
> UserName Text
> PassWord Text
>
> TABLE_COMS
> ID PK
> NameID Indexed
> ComType TinyInt (1=Tel 2=Mobile 3=Fax 4=Email Address etc)
> ComInfo Text(255)
>
> TABLE_ADDRESS
> ID PK
> NameID Indexed
> Addr1 Text
> Addr2 ....
> ........
>
> TABLE_FINANCE
> ID PK
> NameID Indexed
> Bank_NameID Indexed (Banks NameID for Banks address etc)
> BankName
> BankSort
> AccName
> AccNumber
> CCType
> CCName
> CCNumber
> CCExpiry...
> .......

Nope, bad structure. Never keep two different types of data (companies
and individuals) in the same table. Additionally, things like companies
will never have credit cards - individuals will. Yes, it might be a
company credit card - but an individual must authorize the charge. And
companies don't have userid's and passwords.

Something like:

TABLE_COMPANY
CompanyID PK
CompanyName Text

TABLE CONTACT
ContactID PK
AddressID Indexed (points to Address ID)
LastName Text(32) (if Iscompany, company name here)
FirstName Text(32)
Title TinyInt 1=Mr 2=Ms 3=Ltd 4=Plc 5=Edu 6=Gov etc
UserName Text
PassWord Text


TABLE_COMS
ID PK
ContactID Indexed
ComType TinyInt (1=Tel 2=Mobile 3=Fax 4=Email Address etc)
ComInfo Text(255)

TABLE_ADDRESS
ID PK
CompanyID Indexed
Addr1 Text
Addr2 ....
.........

TABLE_FINANCE
ID PK
ContactID Indexed
Bank_NameID Indexed (Banks NameID for Banks address etc)
BankName
BankSort
AccName
AccNumber
CCType
CCName
CCNumber
CCExpiry...
........

This is one way to do it, if the company has multiple addresses. An
alternative would be to have the address in the Company table and if
there are multiple addresses have additional entries. A little less
normalized, but not too much.

Also, do some searching on "database normalization". It will help more
than a lot of other reading.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Kenny]

>Nope, bad structure. Never keep two different types of data (companies

>and individuals) in the same table. Additionally, things like companies
>will never have credit cards - individuals will. Yes, it might be a
>company credit card - but an individual must authorize the charge. And
>companies don't have userid's and passwords.
>
>Something like:
>
>TABLE_COMPANY
>CompanyID PK
>CompanyName Text
>
>TABLE CONTACT
>ContactID PK
>AddressID Indexed (points to Address ID)
>LastName Text(32) (if Iscompany, company name here)
>FirstName Text(32)
>Title TinyInt 1=Mr 2=Ms 3=Ltd 4=Plc 5=Edu 6=Gov etc
>UserName Text
>PassWord Text
>
>
>TABLE_COMS
>ID PK
>ContactID Indexed
>ComType TinyInt (1=Tel 2=Mobile 3=Fax 4=Email Address etc)
>ComInfo Text(255)
>
>TABLE_ADDRESS
>ID PK
>CompanyID Indexed
>Addr1 Text
>Addr2 ....
>........
>
>TABLE_FINANCE
>ID PK
>ContactID Indexed
>Bank_NameID Indexed (Banks NameID for Banks address etc)
>BankName
>BankSort
>AccName
>AccNumber
>CCType
>CCName
>CCNumber
>CCExpiry...
>.......
>
>This is one way to do it, if the company has multiple addresses. An
>alternative would be to have the address in the Company table and if
>there are multiple addresses have additional entries. A little less
>normalized, but not too much.
>
>Also, do some searching on "database normalization". It will help more
>than a lot of other reading.

Awesome Jerry - thank you so much for the clarity and rapid response.

The company v individuals thing always troubled me (was trying to
ensure that only a search on a single field would always pull up the
name, whether it was a company or person).

I must admit, by using 4 tables I thought I was 'normalizing' the
design (grins), so I will do the search you suggest.

Thanks again man.

[Kenny]

>>TABLE_COMPANY
>>CompanyID PK
>>CompanyName Text
>>
>>TABLE CONTACT
>>ContactID PK
>>AddressID Indexed (points to Address ID)
>>LastName Text(32) (if Iscompany, company name here)
>>FirstName Text(32)
>>Title TinyInt 1=Mr 2=Ms 3=Ltd 4=Plc 5=Edu 6=Gov etc
>>UserName Text
>>PassWord Text
>>
>>TABLE_ADDRESS
>>ID PK
>>CompanyID Indexed
>>Addr1 Text
>>Addr2 ....
>>........

Geeting to grips with Normalization - another question if you have
time -

When using two tables for names, say TABLE_COMPANY and TABLE_CONTACT
does this imply two tables are required for addresses eg,
TABLE_COMPANY_ADDR and TABLE_CONTACT_ADDR or is it more efficient to
use one table, but with two keys, eg, especially if they might share
the same address

TABLE_ADDRESS
ID PK
CompanyID Indexed
ContactID Indexed
Addr1 Text
Addr2 ....
.........

Many thanks

[Paul Lautman]

Kenny wrote:

>>> TABLE_COMPANY
>>> CompanyID PK
>>> CompanyName Text
>>>
>>> TABLE CONTACT
>>> ContactID PK
>>> AddressID Indexed (points to Address ID)
>>> LastName Text(32) (if Iscompany, company name here)
>>> FirstName Text(32)
>>> Title TinyInt 1=Mr 2=Ms 3=Ltd 4=Plc 5=Edu 6=Gov etc
>>> UserName Text
>>> PassWord Text
>>>
>>> TABLE_ADDRESS
>>> ID PK
>>> CompanyID Indexed
>>> Addr1 Text
>>> Addr2 ....
>>> ........

>
> Geeting to grips with Normalization - another question if you have
> time -
>
> When using two tables for names, say TABLE_COMPANY and TABLE_CONTACT
> does this imply two tables are required for addresses eg,
> TABLE_COMPANY_ADDR and TABLE_CONTACT_ADDR or is it more efficient to
> use one table, but with two keys, eg, especially if they might share
> the same address
>
> TABLE_ADDRESS
> ID PK
> CompanyID Indexed
> ContactID Indexed
> Addr1 Text
> Addr2 ....
> ........
>
> Many thanks

An address is a known entity that is common for both companies and contacts
so just have one table.

[Kenny]

>An address is a known entity that is common for both companies and contacts
>so just have one table.

Paul, that helped build my confidence little more. I am trying to
write an Events & Ticketing database and am now beginning to realize
what an intensely intellectual demand is required for my largest, but
what I 'thought' was a straightforward project (but its good for me).

I'm becoming keenly aware of the level of knowledge that you real
database programmers require, obviously hard earned expertise - so I
hope both yourself and Jerry Stuckle are aware of just how much this
sort of help is appreciated.

Many thanks

Ken

[Jerry Stuckle]

Kenny wrote:

>>Nope, bad structure. Never keep two different types of data (companies
>>and individuals) in the same table. Additionally, things like companies
>>will never have credit cards - individuals will. Yes, it might be a
>>company credit card - but an individual must authorize the charge. And
>>companies don't have userid's and passwords.
>>
>>Something like:
>>
>>TABLE_COMPANY
>>CompanyID PK
>>CompanyName Text
>>
>>TABLE CONTACT
>>ContactID PK
>>AddressID Indexed (points to Address ID)
>>LastName Text(32) (if Iscompany, company name here)
>>FirstName Text(32)
>>Title TinyInt 1=Mr 2=Ms 3=Ltd 4=Plc 5=Edu 6=Gov etc
>>UserName Text
>>PassWord Text
>>
>>
>>TABLE_COMS
>>ID PK
>>ContactID Indexed
>>ComType TinyInt (1=Tel 2=Mobile 3=Fax 4=Email Address etc)
>>ComInfo Text(255)
>>
>>TABLE_ADDRESS
>>ID PK
>>CompanyID Indexed
>>Addr1 Text
>>Addr2 ....
>>........
>>
>>TABLE_FINANCE
>>ID PK
>>ContactID Indexed
>>Bank_NameID Indexed (Banks NameID for Banks address etc)
>>BankName
>>BankSort
>>AccName
>>AccNumber
>>CCType
>>CCName
>>CCNumber
>>CCExpiry...
>>.......
>>
>>This is one way to do it, if the company has multiple addresses. An
>>alternative would be to have the address in the Company table and if
>>there are multiple addresses have additional entries. A little less
>>normalized, but not too much.
>>
>>Also, do some searching on "database normalization". It will help more
>>than a lot of other reading.

>
>
> Awesome Jerry - thank you so much for the clarity and rapid response.
>
> The company v individuals thing always troubled me (was trying to
> ensure that only a search on a single field would always pull up the
> name, whether it was a company or person).
>
> I must admit, by using 4 tables I thought I was 'normalizing' the
> design (grins), so I will do the search you suggest.
>
> Thanks again man.
>
>

Kenny,

This is not an easy question. Is this a business address? If so, then
the address should be related to your Company table (a company could
have multiple addresses). However, if this is an individual's address,
then it should be related to the Contact table.

If the latter, I think it should be a separate table. The reason being
- if it's an individual address vs. a company address, it really is
something different. That way, if you want to search for any contact in
Podunk Center, Ia, you only have to relate to the Company table.

OTOH, you could have both company and contact addresses in the same
table, but that makes things a lot more complicated. If you have a
contact in Podunk Center, Ia, you need to check both company and contact
tables for the match.

The other problem you run into is - Contact and Company may have
duplicate primary keys (i.e. you may have a PK of "3" in both tables).
Which one would the address refer to?

Normalization is not an exact science. But generally, when you have a
subservient table such as Addresses, it's not good to have two master
tables referring to it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Kenny]

On Wed, 16 Aug 2006 23:40:29 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:

>This is not an easy question. Is this a business address? If so, then
>the address should be related to your Company table (a company could
>have multiple addresses). However, if this is an individual's address,
>then it should be related to the Contact table.

Jerry - your original response kicked me into life, made me recall
important philosophical axioms from uni days like "An organisation is
an abstract entitiy - its really a collection of people" and also made
me realize how everyday items may have to be viewed very differently
when placed in the context of relational database.

>The other problem you run into is - Contact and Company may have
>duplicate primary keys (i.e. you may have a PK of "3" in both tables).
>Which one would the address refer to?

....

>Normalization is not an exact science. But generally, when you have a
>subservient table such as Addresses, it's not good to have two master
>tables referring to it.

Mindreader - this is exactly the problem I'm now grappling with (once
I accepted that there is a damn good reason for so many tables in a
database - where I once thought a couple of tables would suffice).

My mental block (before reading posts from you guys) was I sort of
instinctively felt that a 'master' names table, with a 'type' field
was the way forward. Thus, whether the query was on a Company,
Individual, Supplier, Bank, Employee, even Favourite Restaurant, etc -
only one 'names' table would be consulted for email, tel, password,
etc.

How naive, now I understand how many issues there are involved.

I do try hard to research before ever posting, but some things get us
newbies every time - so I have an 'inexact science' type question.

I understand there can be a threat to autonumber (in some database
manipulations). The proposed solution seems to usually involves using
a separate table to hold PK/s and incrementing. Us newbies feel the
threat must still be very real, if the new PK's values are still in a
table within the db, ie; still subject to the databases integrity.

Which way (on balance) do you feel should one err -

(1) ALL self-generated PK's
(2) ALL Autonumber PK's
(3) Mixture of both, based on preservation criticality.

Many thanks

Kenny

[Jerry Stuckle]

Kenny wrote:

> On Wed, 16 Aug 2006 23:40:29 -0400, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>
>

>>This is not an easy question. Is this a business address? If so, then
>>the address should be related to your Company table (a company could
>>have multiple addresses). However, if this is an individual's address,
>>then it should be related to the Contact table.

>
>
> Jerry - your original response kicked me into life, made me recall
> important philosophical axioms from uni days like "An organisation is
> an abstract entitiy - its really a collection of people" and also made
> me realize how everyday items may have to be viewed very differently
> when placed in the context of relational database.
>
>

>>The other problem you run into is - Contact and Company may have
>>duplicate primary keys (i.e. you may have a PK of "3" in both tables).
>>Which one would the address refer to?

>
> ...
>

>>Normalization is not an exact science. But generally, when you have a
>>subservient table such as Addresses, it's not good to have two master
>>tables referring to it.

>
>
> Mindreader - this is exactly the problem I'm now grappling with (once
> I accepted that there is a damn good reason for so many tables in a
> database - where I once thought a couple of tables would suffice).
>
> My mental block (before reading posts from you guys) was I sort of
> instinctively felt that a 'master' names table, with a 'type' field
> was the way forward. Thus, whether the query was on a Company,
> Individual, Supplier, Bank, Employee, even Favourite Restaurant, etc -
> only one 'names' table would be consulted for email, tel, password,
> etc.
>
> How naive, now I understand how many issues there are involved.
>
> I do try hard to research before ever posting, but some things get us
> newbies every time - so I have an 'inexact science' type question.
>
> I understand there can be a threat to autonumber (in some database
> manipulations). The proposed solution seems to usually involves using
> a separate table to hold PK/s and incrementing. Us newbies feel the
> threat must still be very real, if the new PK's values are still in a
> table within the db, ie; still subject to the databases integrity.
>
> Which way (on balance) do you feel should one err -
>
> (1) ALL self-generated PK's
> (2) ALL Autonumber PK's
> (3) Mixture of both, based on preservation criticality.
>
> Many thanks
>
> Kenny

I use autoincrement fields.

First of all, if possible, you want to use integer fields for PK's
because they are faster to compare than strings.

MySQL's autoincrement field is good. You won't get duplicate numbers,
even if two different users insert at the same time. And the value
returned from the mysql_insert_id() is for the last insert from this
connection, so you won't get someone else's value. A good
implementation, overall.

The only time I don't use autoincrement columns is for things like
customer id, member id, etc., where the id is being supplied by the user
(and is an integer).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Kenny]

On Thu, 17 Aug 2006 16:20:58 -0400, Jerry Stuckle
<jstucklex@attglobal.net> wrote:

>
>I use autoincrement fields.
>
>First of all, if possible, you want to use integer fields for PK's
>because they are faster to compare than strings.
>
>MySQL's autoincrement field is good. You won't get duplicate numbers,
>even if two different users insert at the same time. And the value
>returned from the mysql_insert_id() is for the last insert from this
>connection, so you won't get someone else's value. A good
>implementation, overall.
>
>The only time I don't use autoincrement columns is for things like
>customer id, member id, etc., where the id is being supplied by the user
>(and is an integer).

Perfect Jerry - thank you.

Can I ask one more question -

Say you have a very basic example 'Clients' database with 2 tables,

tbl_names tbl_addr

nameID addrID
addrID addr1
lastname addr2
firstname zip

If 'Clients' addresses are never (or rarely) duplicated, does
normalizing really significantly improve the speed and data storage?
(It certainly increases web page coding and record manipulation
complexity.)

In 'that particular scenario', I need to convince myself that to
normalize it really is still beneficial, and that in this instance -

names
----------
nameID
lastname
addr1
addr2
zip

could actually be a better approach.

I ask because I 'thought' that in most relational databases, when Text
field/s are empty, the storage is not taken up (apart from some
defined overhead) and with two tables, even a SELECT query should take
longer to parse in the query and then open and close '2' tables.

I am also thinking that although the addr fields would be in the same
table, anything like "SELECT nameID, lastname FROM names ORDER BY
lastname" still would still be as fast, because the fields are not
selected.

Thanks again for your time Jerry

[Jerry Stuckle]

Kenny wrote:

> On Thu, 17 Aug 2006 16:20:58 -0400, Jerry Stuckle
> <jstucklex@attglobal.net> wrote:
>
>

>>I use autoincrement fields.
>>
>>First of all, if possible, you want to use integer fields for PK's
>>because they are faster to compare than strings.
>>
>>MySQL's autoincrement field is good. You won't get duplicate numbers,
>>even if two different users insert at the same time. And the value
>>returned from the mysql_insert_id() is for the last insert from this
>>connection, so you won't get someone else's value. A good
>>implementation, overall.
>>
>>The only time I don't use autoincrement columns is for things like
>>customer id, member id, etc., where the id is being supplied by the user
>>(and is an integer).

>
>
> Perfect Jerry - thank you.
>
> Can I ask one more question -
>
> Say you have a very basic example 'Clients' database with 2 tables,
>
> tbl_names tbl_addr
>
> nameID addrID
> addrID addr1
> lastname addr2
> firstname zip
>
> If 'Clients' addresses are never (or rarely) duplicated, does
> normalizing really significantly improve the speed and data storage?
> (It certainly increases web page coding and record manipulation
> complexity.)
>
> In 'that particular scenario', I need to convince myself that to
> normalize it really is still beneficial, and that in this instance -
>
> names
> ----------
> nameID
> lastname
> addr1
> addr2
> zip
>
> could actually be a better approach.
>
> I ask because I 'thought' that in most relational databases, when Text
> field/s are empty, the storage is not taken up (apart from some
> defined overhead) and with two tables, even a SELECT query should take
> longer to parse in the query and then open and close '2' tables.
>
> I am also thinking that although the addr fields would be in the same
> table, anything like "SELECT nameID, lastname FROM names ORDER BY
> lastname" still would still be as fast, because the fields are not
> selected.
>
> Thanks again for your time Jerry
>
>

Kenny,

Well, there's a big difference between "never" and "rarely".

If they are NEVER duplicated, keep everything in a single table. No
need to create a new table when you have a 1:1 correspondence, IMHO.

"Rarely" is different. It falls between "never" and "often" - where you
should have separate tables.

A "pure" design (3rd Normal) would put address in a separate table.
However, at the same time, that adds some overhead to join the tables.
If you have very few duplicates, I see no reason not to use a single
table. No, it's not 3rd Normal form. And you might have a limited
amount of duplicated data. But access will be faster (no joins for
SELECT and a single INSERT/UPDATE can handle both name and address).

It's part of the reason why database design is not an exact science :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Murdoc]

Kenny wrote:

>

> > > TABLE_COMPANY
> > > CompanyID PK
> > > CompanyName Text
> > >
> > > TABLE CONTACT
> > > ContactID PK
> > > AddressID Indexed (points to Address ID)
> > > LastName Text(32) (if Iscompany, company name here)
> > > FirstName Text(32)
> > > Title TinyInt 1=Mr 2=Ms 3=Ltd 4=Plc 5=Edu 6=Gov etc
> > > UserName Text
> > > PassWord Text
> > >
> > > TABLE_ADDRESS
> > > ID PK
> > > CompanyID Indexed
> > > Addr1 Text
> > > Addr2 ....
> > > ........

>
> Geeting to grips with Normalization - another question if you have
> time -
>
> When using two tables for names, say TABLE_COMPANY and TABLE_CONTACT
> does this imply two tables are required for addresses eg,
> TABLE_COMPANY_ADDR and TABLE_CONTACT_ADDR or is it more efficient to
> use one table, but with two keys, eg, especially if they might share
> the same address
>
> TABLE_ADDRESS
> ID PK
> CompanyID Indexed
> ContactID Indexed
> Addr1 Text
> Addr2 ....
> ........
>
> Many thanks

You should store the AddressID on the Company/Contact record (if there is a maximum of one per entity). If not, then introduce an intermediate table (Address_Link, for example) that stores:

Entity_Type VARCHAR
Entity_ID INTEGER
AddressID INTEGER

Using this allows for multiple address for a given entity (company or contact), and also allows a single Address record to be linked across multiple entities (which simplies updates).

--

[Kenny]

>"Rarely" is different. It falls between "never" and "often" - where you

>should have separate tables.

Never thought about it, just always used the word (grins) - That is a
very good point, just 'how rare' is rare!

>It's part of the reason why database design is not an exact science :-)

Thank you Jerry, its this 'black art' side of computing that always
forces us newbies to hunt down you guys for intellectual favors.

Many thanks man - loads of things a lot clearer now - will leave you
in peace for a bit.

Ken