SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Procrastinator Extraordinaire lucas's Avatar
    Join Date
    Oct 2000
    Location
    Springfield, Missouri, U.S.A.
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Relational database design question

    i'm developing a system that uses transactions to relate tables called equipment to customers.

    obviously this is a many-to-many relationship and there needs to be a table inbetween these tables which i will call lineItem.

    now i believe there should be yet another table that i will call transactions. within this transactions table will be a record for each transaction that takes place. so transactions holds a record for each transaction, lineItem holds a record for each item within the transaction.

    i think i should relate the customer table directly to the transaction table. am i right? if i were to relate it to the lineItem table, then i would have a lot of redundant information.

    here is a link to a diagram that i made:
    http://extremesanity.com/db%20question.gif

  2. #2
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can there be more than one item per transaction? If so, your design is fine. If not, then you can combine your "item" and "transactions" tables.

    Incidentally, you should really name your tables consistently with regard to pluralisation (I recommend singular, but choose and stick with it).
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  3. #3
    Procrastinator Extraordinaire lucas's Avatar
    Join Date
    Oct 2000
    Location
    Springfield, Missouri, U.S.A.
    Posts
    320
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, there can be more than one item per transaction, thats why i felt the need to split it up into the transaction and item tables.

    and i was sticking with the singular form for all of my tables, until i found out that "transaction" even if it is a database table is a reserved word and causes all kinds of weird errors to occur.

  4. #4
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just put square brackets around the table/field names in your SQL statements to tell the database engine that they are not functions, etc. I do this anyway, because I find the SQL looks tidier!

    Code:
    SELECT [ID], [Name] FROM [Transaction] WHERE [ID] = 5;
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


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
  •