Leaderboard

Pretty much. The columns represent discrete identifiers that have no intrinsic numerical properties, meaning there's no reason you should ever do anything based on an order or product ID range, therefore all your queries will be straight equality - whether the row is or is not for a particular order and/or product. As such there are no benefits to having dual indexes on both columns.

More precisely, the query analyzer will only use an index if the first N columns in it are included in the query. Given indexes (columnA), (columnB), and (columnA, columnB), SELECT * FROM table WHERE columnA = 123 could use the (columnA) or (columnA, columnB) indexes. SELECT * FROM table WHERE columnA = 123 AND columnB = 456 could use the (columnA) or (columnB) or (columnA, columnB) indexes - and the last one is probably best. Note that (columnA) and (columnA, columnB) are redundant: anything that can use the first one could also use the second one. There's more to it than just about being "common", but that's a decent starting point. If you wanted to query for products across all orders. Point #1: An index takes up disk space to store the data. Point #2: Each index slightly reduces performance when making changes to table data because the system has to update its indexes. Point #3: (order_id, product_id) and (product_id, order_id) are mostly redundant. Conclusion: I don't think there's enough reason to create both. Addendum: Actually it kinda depends on the data. But mostly I'm trying to discourage the "moar indexes moar columns" mentality.

I wouldn't split the clients into vendor and client tables - just have a client table. In one transaction, A might be the seller but in another transaction A might be the buyer (as in the examples in your initial post). Just record the buyer and seller ids in each sale transaction. Don't store derived data, such as total paid, balance outstanding etc. You get those by querying the transactions and payments.

Your items table may be a headache. Vendor B sells bikes and the item attributes are Now what if vendor D sells fridge/freezers whose attributes are width, height, depth, colour, fridge capacity, freezer capacity, ice dispenser(Y/N) and vendor E is selling concert tickets?

What fields do you think should go in which tables, and what fields are you not sure about?