SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help With Some Database Logic

    Hello,

    I am trying to construct a Database for a client.

    He has 3 tables, Customers, Clients, Suppliers.
    Code:
    Primary Keys:
    Customers (customerNumber, AutoNumber)
    Orders (orderNumber, AutoNumber)
    Suppliers (supplierNumber, AutoNumber)
    
    Relationships
    Customers to Orders (via customerNumber)
    Suppliers to Orders (via supplierNumber)
    Now with his current database in the Customer Table he has Account Numbers in field Names.

    For Example one fieldname is IBM_account_num, another is MICROSOFT_account_num. We want to design it so those fields dont have to be hard coded.

    If he were to open an account with DELL, the only way to update the DB is add another field. There must be another way.

    I am thinking adding a 4th table and calling it Accounts and relating the Customer and Suppliers tables through it simular to the way Orders was.
    Code:
    Primary Keys:
    Accounts (accountId, AutoNumber)
    
    Relationships
    Customers to Accounts (via customerNumber)
    Suppliers to Accounts (via supplierNumber)
    Orders to Accounts (via orderNumber)
    Is this kind of logic ok, or am I heading in the wrong direction?

    Thanks

  2. #2
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You've got the right idea.

    A customer can have many accounts.
    An account belongs to only one customer.

    = One-To-Many relationship.

    This means accounts should be its own table.

  3. #3
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Awsome, I was just having a heck of a time getting my head around this.

    I can work with DBs but have never had to build one before.

    Thanks


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
  •