CODEWITHSUNDEEP
mysql
beginner
beginner

Reputation: 2508

Sql creating table without using foreign key

Consider we have one sql table customersenter image description here

now consider iF we have a table where their are two columns customer_name and orders_name now one customer may have multiple orders (one to many relationship) So we have table where in which we choose customer_name as foriegn key. But now consider we have 100 orders to one customer_name so we have to write same customer_name 100 times. waist of memory.

customer_name,customer_orders table is

enter image description here
so i was thinking is can't we just make table with name of customer_name orders, for examle if we have customer_name bill so we can create a table with name of bill's orders, and write all his orders in it, now we not using any foriegn key,

bill's orders table is

enter image description here

and more tables we can create for other users so how it is possible to delete the table when we delete that customer_name from main table. any idea?

Upvotes: 0

Views: 40

Answers (1)

Looter
Looter

Reputation: 96

You solve the issue of wasted space by using surrogate keys. Instead of copying a huge alphanumeric field (names) to child tables, you would create an ID of sorts using a more compact data type (byteint, smallint, int, etc.). In the approach you propose where you create a separate table for each customer, you will run into the following issues:

  • cannot run aggregates across customers, i.e., you cannot simply do a sum, avg, min, etc. for sets of customers slicing the data different ways
  • SQL will be far more complex with each extra customer added to the queries
  • your data dictionary is going to grow huge and at some point you will incur major performance issues that are not easy to fix

The point of using a relational database is to allow for users to dynamically slice and dice the data. The method that you are proposing would not be useful for querying.

Upvotes: 2

Related Questions