Alternatives to junction table?

Question

I'm designing a relational database tables for storing data about eCommerce scenario where I need to store

• List of Products purchased by a user
• List of users who purchased a particular product.

Its a many to many relationship.

So far I could only thinking of doing this.
create a table for storing orders

    table recordorders(
       userID // foreign key from users table
       productID, // foreign key from products table
       dateofpurchase,
       quantity,
       price_per_unit,
       total_amount
    )

It will act like a junction table.

Is this a good approach and are there any other methods than junction table that are more effective and efficient for querying ?

Answer 1

Your bullets describe two tables, not one. Your junction table is not properly described as two lists. It is a set of order info rows. The junction table you gave holds rows where "user [userID] purchased product [productID] on ...". Ie it records order info. (Combinations of user, product, date, etc of orders.) Given a user or product, you can get the corresponding bullet table by querying the order info table.

However your table probably needs another column that is a unique order id. Otherwise it cannot record that there are two orders that are alike in all those columns. (Eg if the same person buys the same product on the same date in the same quantity, price and total.) Ie its rows probably aren't 1:1 with orders. That's why above I called it an order info table rather than an order table. It records that some order had those properties; but it doesn't record distinct orders if there can be orders with the same info. It's really a many-to-many-to-etc (for every column) association. That is why an order id gets picked as a unique name for an order as further info. This new table would be called an entity table, not a junction or association table. It holds rows where "in order [id] user [user] purchased ...".

PS An order is usually something that can be characterized as an association on/among/between an order id, user, set of order-lines (product, quantity, price & total), and other stuff (date, grand total, etc). The orders are usually relationally characterized by an order entity table on order id with its user, date etc plus an order-line association table on order ids and their order-line info.

PPS It's time for you to read a book about information modeling and database design.