Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2008
    Posts
    2

    Schema for Company database

    Hi all,
    I am new here and also newbie to databases.

    I am trying to create a database that will hold information about who owns a company. Something like this:

    A company can be own by one or more persons or other companies. Ex.
    Google is owned by:
    Person1
    Person2
    Company1
    Company2

    Futhermore I would like to keep track of the history, how the ownership change over time.

    How can this be done?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    2 googles 4 u 2 do: party datamodel and supertype/subtype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    One possibility:

    CREATE TABLE CompanyOwners_People
    (CompanyId INT NOT NULL REFERENCES Companies (CompanyId),
    Owner_PersonId INT NOT NULL REFERENCES People (PersonId));

    CREATE TABLE CompanyOwners_Companies
    (CompanyId INT NOT NULL REFERENCES Companies (CompanyId),
    Owner_CompanyId INT NOT NULL REFERENCES Companies (CompanyId));

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Oops. Just in case it wasn't obvious:

    CREATE TABLE CompanyOwners_People
    (CompanyId INT NOT NULL REFERENCES Companies (CompanyId),
    Owner_PersonId INT NOT NULL REFERENCES People (PersonId),
    PRIMARY KEY (CompanyId, Owner_PersonId));

    CREATE TABLE CompanyOwners_Companies
    (CompanyId INT NOT NULL REFERENCES Companies (CompanyId),
    Owner_CompanyId INT NOT NULL REFERENCES Companies (CompanyId)
    PRIMARY KEY (CompanyId, Owner_CompanyId));

  5. #5
    Join Date
    Jul 2008
    Posts
    2
    This seems to work fine. Thank you. I did a quick try on OpenOffice - Base but it seems that the program does not like the double relation between "CompanyOwners_Companies" and "Companies". It allows me to define it but when I save it and reopen it's gone. Is this the normal behavior for this program or am I doing something wrong.

Posting Permissions

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