I have a table called Pricing.
Item x 10
Itemid itemname itembrand
Customer X 20
Custid custname custdetails
Pricing
pId itemId custId price
If itemId has 10 records and customer has 20 records. Data entry will be troublesome as one customer can buy 10 items each in total there will be 200 records in the table. How can I make the table more efficient instead of entering 200 records?
What makes you think that 200 records is "inefficient"?
You have 20 customers - one record each.
You have 10 items - one record each.
If all 20 customers order one of each item, then yes, you will need 20 x 10 = 200 records to represent that and there is absolutely nothing wrong with that.
This is the way that Relational databases like to work.
Databases are generally very good at finding small bits of "stuff" and putting them together.
They are generally pretty rubbish at taking large blocks of "stuff", like arrays, and pulling them apart.
DO NOT be tempted to think about "condensing" Items (or Customers) into arrays.
While this might seem sensible from a coding point of view, it is absolutely the wrong way to do it at the database level. Whichever way you do the "grouping", you will always find some need to query it the "other way round" and then it all falls apart or gets horribly complicated. Storing the data so that it can be queried either way, i.e. storing it atomically in your 200 records, is far more efficient for the database to work with.
Also remember that while you may write your query to look at one table first, then join to another and then another, there is nothing to stop the database executing that query using the tables in a completely different order. That's the Power of Relational databases.
