Professional Web Applications Themes

Newbie 'structure' design query - MySQL

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

  1. #1

    Default 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...
    ........
    Kenny Guest

  2. #2

    Default Re: Newbie 'structure' design query

    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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: Newbie 'structure' design query

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

  4. #4

    Default Re: Newbie 'structure' design query

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

  5. #5

    Default Re: Newbie 'structure' design query

    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.


    Paul Lautman Guest

  6. #6

    Default Re: Newbie 'structure' design query

    >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


    Kenny Guest

  7. #7

    Default Re: Newbie 'structure' design query

    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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  8. #8

    Default Re: Newbie 'structure' design query

    On Wed, 16 Aug 2006 23:40:29 -0400, Jerry Stuckle
    <jstucklexattglobal.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
    Kenny Guest

  9. #9

    Default Re: Newbie 'structure' design query

    Kenny wrote:
    > On Wed, 16 Aug 2006 23:40:29 -0400, Jerry Stuckle
    > <jstucklexattglobal.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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  10. #10

    Default Re: Newbie 'structure' design query

    On Thu, 17 Aug 2006 16:20:58 -0400, Jerry Stuckle
    <jstucklexattglobal.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 p 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 Guest

  11. #11

    Default Re: Newbie 'structure' design query

    Kenny wrote:
    > On Thu, 17 Aug 2006 16:20:58 -0400, Jerry Stuckle
    > <jstucklexattglobal.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 p 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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  12. #12

    Default Re: Newbie 'structure' design query

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

    --

    Murdoc Guest

  13. #13

    Default Re: Newbie 'structure' design query

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

    Kenny Guest

Similar Threads

  1. Copy a table in Access structure only with CF query
    By jimWPX in forum Coldfusion Database Access
    Replies: 3
    Last Post: June 4th, 02:33 PM
  2. Query w/bad table structure
    By rmorgan in forum Coldfusion Database Access
    Replies: 9
    Last Post: March 2nd, 06:35 PM
  3. Accessing a Query in a structure
    By mattw in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: May 6th, 04:32 PM
  4. Query to Structure?
    By WestSide in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 2nd, 09:45 AM
  5. query to return table structure
    By fxthomas in forum Informix
    Replies: 4
    Last Post: September 17th, 04:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139