Newbie 'structure' design query

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 

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.

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. 

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

On Wed, 16 Aug 2006 23:40:29 -0400, Jerry Stuckle




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.

...

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.
 XXXX@XXXXX.COM 
==================

On Thu, 17 Aug 2006 16:20:58 -0400, Jerry Stuckle




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.
 XXXX@XXXXX.COM 
==================

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

--

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

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


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

That's why they get the big bucks... well, sometimes. Other times
they're just happy to have someone actually listen to 'em.

-- 
6. I will not gloat over my enemies' predicament before killing them.
                --Peter Anspach's list of things to do as an Evil Overlord