Relational database design question

[lucas]

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

[MarcusJT]

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).

[lucas]

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.

[MarcusJT]

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!

SELECT [ID], [Name] FROM [Transaction] WHERE [ID] = 5;