SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27

Thread: Database Design

  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Design

    Can someone help me with my database design.

    I want to create an application like a client area. In this area there are several sections, and in each of these sections there are going to be documents like pdf or word files or whatever.

    When a user logs in to there area they will find the documents relevant to them and these documents can be found in the relevant sections.

    This is the table structure I have:

    Clients: client_id, client, username, password
    Documents: document_id, doc_type, doc_name
    Document Type: type_id, type

    Do I have to join the tables up?

    What is the best way?

  2. #2
    SitePoint Evangelist comfixit's Avatar
    Join Date
    Dec 2004
    Location
    Pasadena
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see two problems with your table structure.

    #1 In documents you should be using type_ID to be consistent with your other type table.
    #2 You need another table to associate documents with a given user. I am not sure exactly the effect your trying to go for.

    Can a single document be associated with multiple users or is there only one user per document like there is typicaly only a single author.

    If there is only one individual per document then you can add a field for userID to document. On the other hand if its a many to many relationship ie. multiple users could have the same document then you need a seperate table.

    For example:
    UserDocList: UDL_ID, client_id, document_id

    Then when you load a user, you can then do a select query for for all UDL (UserDocList) entries where client_id = x

    Then you look through each entry and do a select query to list the document where document_id = y

    Hope that makes sense.

    Two other fields you should consider are a date published field (but dont insert that till the end cause Dates can be annoying when coding) and a link filed with a path to the document.

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I should have a field type_id in both Documents and Document Type tables.

    Each document will be exclusive to the client, so can I have a client_id in both the document and client tables?

    How would I JOIN them?

  4. #4
    SitePoint Evangelist comfixit's Avatar
    Join Date
    Dec 2004
    Location
    Pasadena
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The basic way to bind the tables is to use what’s known as a primary key and a foreign key.

    A primary key is just a unique ID for every row of a table.
    A foreign key is a reference to the primary key in another table.

    So when you have a database that has information like lets say associating a document to an individual user only.

    UserTbl : UserID, UserName
    DocTbl : DocID, DocName, UserID

    In this simplified example you could have several documents, each would have a unique DocID but many may share the same UserID.

    Eventually you decide that you want to list all the documents with a given userID.

    Ok so you would do a Select statement via SQL. For example:

    Select * from DocTbl where userID = [myVar]

    Where the brackets enclose the statement in your scripting language of choice. That statement will then return only documents that are associated with that user.

    Inner Joins and Outer Joins etc... can be used for certain types of data integrity operations. For what your doing you may just want to try and normalize your data as much as possible and use the primary key/foreign key method of connecting your data.

    Your data is not really binded together. Its more or less just laying around, you just need a way to make sure you can let your scripts search through it efficiently.

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would I normalize the data by using the foreign/primary key method.

  6. #6
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can I have a client_id in clients table and client_id in documents table, and then when I upload a document from a web form, I can pass the client_id from the client table to the client_id in the document table, and then when I look at the client area that matches that client_id from the client table it contains all the documents that match that of the client_id.

    Say that if your drunk!!!

    Am i on the right track there?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, that sounds exactly right, and i'm not even drunk

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so how would I display a document that is relevant to a certain section of the client area that is also relevant to the client?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how? with a query, i guess

    but i don't see "section of the client area" and i don't understand "relevant" --

    Clients: client_id, client, username, password
    Documents: document_id, doc_type, doc_name
    Document Type: type_id, type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok fair enough. I have a client area that a ckient logs into, he/she may have various different sections of their client area, and in that client area there will be documents that they can download. Each document is exclusive to the client so basically I would want to match up the client with the relevant document in the relevant section.

    Could I not pass the client_id to the document when I upload the document, and then when I am in say section D of client 1's area it will show me documents that match the client_id and the document_id.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    can a document belong to more than one client?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, it is there document only. This is the URL where the client area will be,

    http://www.b2bnative.com/test/clientArea_test.php

    [u] native
    [p] native

    There will be 9 sections(type_id)

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    suggested tables (primary key in bold, foreign key in red) --

    Clients: client_id, client_name
    ClientUsers: client_id, username, password
    Documents: document_id, type_id, client_id, doc_name
    DocumentTypes: type_id, type_name

    query to retrieve documents for a client user --
    Code:
    select C.client_name
         , D.doc_name
         , DT.type_name
      from ClientUsers  as CU
    inner
      join Clients  as C
        on CU.client_id = C.client_id       
    inner
      join Documents  as D
        on C.client_id = D.client_id
    inner
      join DocumentTypes  as DT
        on D.type_id = DT.type_id
     where CU.username = 'username'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A couple of questions:

    Why do I need a seperate table for the clients and clientusers, and is this alias thing a really important element of database design?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you want a separate table for clientusers because some day you anticipate that there might be a need for two people from the same client company to be able to log in

    the aliases are necessary for writing queries only, they are not involved in database design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so I could have two or more people from Nike or Sony wanting to log in to my client area system and view the documents (bills), they will have the same client_id but a different name, that sounds ok. So it really is that simple?

    So in my admin section where I want to upload the documents I make sure that I pass the client_id

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yep, that's right, you're getting the hang of this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent, well i'll keep at it. I'll get back to you if I have any problems. Cheers

  19. #19
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If in the documents table I may want to have a field where I can select a document type, using a drop down menu, do I use enum? and what other options?

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    stay away from enum, enum is the spawn of the devil

    populate your dropdown from the DocumentTypes table (see above)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So how would I show records that match type_id and client_id

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't understand the question

    what does "how would I show records" mean?

    you would show them with php, presumably
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I mean I want to display a record that matches two criterias, client_id and type_id

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i still don't get it

    you run a query to find it, and then you display it



    the query in post #13 will do, with the appropriate change in the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Port Sunlight
    Posts
    815
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This query:

    SELECT *
    FROM documents
    WHERE type_id = 5

    Show's all the records that match type_id =5

    This query:

    SELECT *
    FROM documents
    WHERE client_id = colname

    Shows all the records that match the client_id,

    So if I want to view records for client 1 and in type 3 - I see them records
    If I want to view records for client 2 and in type 3 - I see different records.

    I'm sorry if this isn't clear, it is in my head.


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
  •