Why is it better to store data in multiple related table rather than one big table?

Answer 1

Basically to minimize redundancy (unnecessary repetition) of data in the database. By storing only information (fields) that directly relate to each other in each table AND modeling how the tables should relate to each other you also get the maximum flexibility in ways to retrieve information. A simple example would be a table to store INVOICES and a table to store CUSTOMERS. The INVOICE table only has information about an invoice; Inv#, Amount, Date, etc. and The CUSTOMER table information about customers; Name, Address, Phone, etc. An Invoice has one customer but a customer can have many invoices. If we give the customer a unique ID then, in the INVOICE table we need only store that ID (called a foreign key) but with it the database can look up the rest. So you dont store customer name, address, etc over and over again every time a customer gets a new invoice. The process of splitting up data into separate structures is called normalization. Often times data is de-normalized and repeating data allowed for performance or snapshot reasons.