Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Need help with database design

    Hi,

    I need to create a database for document management and my problem is that my companie already have independent tables with information of other entities like Clients, Providers, etc. I need to create a table for documents that need to have a field that represents the company that had sent the document.
    Ex: Document A was sent by Client B and Document C was sent by Provider D.

    How can I desing the relationship between this tables?

  2. #2
    Join Date
    Jun 2009
    Posts
    66
    CREATE TABLE documents
    (
    document_id INT NOT NULL PRIMARY,
    document_name VARCHAR(25) NOT NULL,
    document_added DATETIME NOT NULL DEFAULT = CURRENT_TIMESTAMP
    )

    CREATE TABLE documents_client_sent
    (
    document_sent_id INT NOT NULL PRIMARY KEY,
    document_id INT NOT NULL REFERENCES documents (document_id),
    client_id INT NOT NULL REFERENCES clients (client_id)
    )

    CREATE TABLE documents_provider_sent
    (
    document_sent_id INT NOT NULL PRIMARY KEY,
    document_id INT NOT NULL REFERENCES documents (document_id),
    provider_id INT NOT NULL REFERENCES providers (provider_id)
    )


    To get all documents sent (put this in a view):
    SELECT a.document_id, a.document_sent_id, a.client_id, 'client' AS sent_type FROM [documents_client_sent] AS a
    UNION ALL
    SELECT b.document_id, b.document_sent_id, b.provider_id, 'provider' as sent_type FROM [documents_provider_sent] AS b

Posting Permissions

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