Sql Table Design

Sql Table Design
Nikhil
Published on 2021-08-15 06:29:00

Properties of a well-designed table

A true relational database must go beyond throwing data into a few tables and writing some SQL statements to pull that data out.

At best a badly designed table structure will slow the execution of queries and could make it impossible for the database to function as intended.

A database table should not be considered as just another table; it has to follow a set of rules to be considered truly relational. Academically it is referred to as a 'relation' to make the distinction.

The five rules of a relational table are:

1. Each value is atomic; the value in each field in each row must be a single value.
2. Each field contains values that are of the same data type.
3. Each field heading has a unique name.
4. Each row in the table must have at least one value that makes it unique amongst the other records in the table.
5. The order of the rows and columns has no significance.

A table conforming to the five rules:

Id Name DOB Manager
1 Fred 11/02/1971 3
2 Fred 11/02/1971 3
3 Sue 08/07/1975 2

  • Rule 1: Each value is atomic. Id, Name, DOB, and Manager only contain a single value.
  • Rule 2: Id contains only integers, Name contains text (we could add that it's the text of four characters or less), DOB contains dates of a valid type and Manager contains integers (we could add that corresponds to a Primary Key field in a managers table).
  • Rule 3: Id, Name, DOB, and Manager are unique heading names within the table.
  • Rule 4: The inclusion of the Id field ensures that each record is distinct from any other record within the table.

A badly designed table:

Id Name DOB Name
1 Fred 11/02/1971 3
1 Fred 11/02/1971 3
3 Sue Friday the 18th July 1975 2, 1

  • Rule 1: The second name field contains two values - 2 and 1.
  • Rule 2: The DOB field contains dates and text.
  • Rule 3: There are two fields called 'name'.
  • Rule 4: The first and second records are exactly the same.
  • Rule 5: This rule isn't broken.

ATutorialHub Related Guide

Comments (8)

Leave a Comment

Your email address will not be published. Required fields are marked*

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website