why do we make relationships between tables?

[Guest]

can any one list how making relationships can help?

Thank you

 

[Guest]

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceding any further
with database design. It's a complex subject but suffice to say, if your
database isn't normalized with proper joins and relationships between tables,
you will learn a lot the hard, very hard, way.

 

[Ed Warren]

Simplicity of design (able to add rows, now columns)
Speed up queries
Lower memory usage
Much easier to return results

Example Compare:

Design 1
Table: RowID, Value1, Value2, Value3, Value4, Value5

Data
1 100 200 300 400 500
2 101 205 200 -30 (if not used have to use memory anyway)

Question what are the lowest and highest values?
Any Query written will need to be changed if you decide you need six values

Design 2
Table: Purchases: PurchaseID
Table: Values ValueID, PurchaseID, Value
Relationship (PurchaseID 1-->M PurchaseID)
data
Purchase
1
2

Values
ValueID PurchaseID Value
1 1 100
2 1 200
3 1 300
4 1 400
5 1 500
6 2 101
7 2 205
8 2 -30

Question what are the lowest and highest values?
Simple select, groupby query can return max, min, average, variance,
standarddev in the database or for each purchase.

Query is independent of the number of values in each purchase.

Ed Warren