A beginner s guide to SQL database design

Question

Do you know a good source to learn how to design SQL solutions   Beyond the basic language syntax  I m looking for something to help me understand    What tables to build and how to link them How to design for different scales  small client APP to a huge distributed website  How to write effective   efficient    elegant SQL queries

User · Accepted Answer

I started with this book  Relational Database Design Clearly Explained  The Morgan Kaufmann Series in Data Management Systems   Paperback  by Jan L  Harrington  and found it very clear and helpful  and as you get up to speed this one was good too Database Systems  A Practical Approach to Design  Implementation and Management  International Computer Science Series   Paperback   I think SQL and database design are different  but complementary  skills

User · Answer

Experience counts for a lot  but in terms of table design you can learn a lot from how ORMs like Hibernate and Grails operate to see why they do things  In addition    Keep different types of data separate - don t store addresses in your order table  link to an address in a separate addresses table  for example  I personally like having an integer or long surrogate key on each table  that holds data  not those that link different tables together  e g   m n relationships  that is the primary key  I also like having a created and modified timestamp column  Ensure that every column that you do  where column   val  in any query has an index  Maybe not the most perfect index in the world for the data type  but at least an index  Set up your foreign keys  Also set up ON DELETE and ON MODIFY rules where relevant  to either cascade or set null  depending on your object structure  so you only need to delete once at the  head  of your object tree  and all that object s sub-objects get removed automatically   If you want to modularise your code  you might want to modularise your DB schema - e g   this is the  customers  area  this is the  orders  area  and this is the  products  area  and use join link tables between them  even if they re 1 n relations  and maybe duplicate the important information  i e   duplicate the product name  code  price into your order details table   Read up on normalisation  Someone else will recommend exactly the opposite for some or all of the above  p - never one true way to do some things eh

User · Answer

Head First SQL is a great introduction

User · Answer

Head First SQL is a great introduction

User · Answer

I started out with this article  http   en tekstenuitleg net articles software database-design-tutorial intro html  It s pretty concise compared to reading an entire book and it explains the basics of database design  normalization  types of relationships  very well

User · Answer

Experience counts for a lot  but in terms of table design you can learn a lot from how ORMs like Hibernate and Grails operate to see why they do things  In addition    Keep different types of data separate - don t store addresses in your order table  link to an address in a separate addresses table  for example  I personally like having an integer or long surrogate key on each table  that holds data  not those that link different tables together  e g   m n relationships  that is the primary key  I also like having a created and modified timestamp column  Ensure that every column that you do  where column   val  in any query has an index  Maybe not the most perfect index in the world for the data type  but at least an index  Set up your foreign keys  Also set up ON DELETE and ON MODIFY rules where relevant  to either cascade or set null  depending on your object structure  so you only need to delete once at the  head  of your object tree  and all that object s sub-objects get removed automatically   If you want to modularise your code  you might want to modularise your DB schema - e g   this is the  customers  area  this is the  orders  area  and this is the  products  area  and use join link tables between them  even if they re 1 n relations  and maybe duplicate the important information  i e   duplicate the product name  code  price into your order details table   Read up on normalisation  Someone else will recommend exactly the opposite for some or all of the above  p - never one true way to do some things eh

User · Answer

It s been a while since I read it  so  I m not sure how much of it is still relevant   but my recollection is that Joe Celko s SQL for Smarties book provides a lot of info on  writing elegant  effective  and efficient queries

User · Answer

I really liked this article   http   www codeproject com Articles 359654 important-database-designing-rules-which-I-fo

User · Answer

These are questions which  in my opionion  requires different knowledge from different domains    You just can t know in advance  which  tables to build  you have to know the problem you have to solve and design the schema accordingly  This is a mix of database design decision and your database vendor custom capabilities  ie  you should check the documentation of your  r dbms and eventually learn some  tips  amp  tricks  for scaling   also the configuration of your dbms is crucial for scaling  replication  data partitioning and so on   again  almost every rdbms comes with a particular  dialect  of the SQL language  so if you want efficient queries you have to learn that particular dialect --btw  much probably write elegant query which are also efficient is a big deal  elegance and efficiency are frequently conflicting goals--   That said  maybe you want to read some books  personally I ve used this book in my datbase university course  and found a decent one  but I ve not read other books in this field  so my advice is to check out for some good books in database design

User · Answer

It s been a while since I read it  so  I m not sure how much of it is still relevant   but my recollection is that Joe Celko s SQL for Smarties book provides a lot of info on  writing elegant  effective  and efficient queries

User · Answer

Experience counts for a lot  but in terms of table design you can learn a lot from how ORMs like Hibernate and Grails operate to see why they do things  In addition    Keep different types of data separate - don t store addresses in your order table  link to an address in a separate addresses table  for example  I personally like having an integer or long surrogate key on each table  that holds data  not those that link different tables together  e g   m n relationships  that is the primary key  I also like having a created and modified timestamp column  Ensure that every column that you do  where column   val  in any query has an index  Maybe not the most perfect index in the world for the data type  but at least an index  Set up your foreign keys  Also set up ON DELETE and ON MODIFY rules where relevant  to either cascade or set null  depending on your object structure  so you only need to delete once at the  head  of your object tree  and all that object s sub-objects get removed automatically   If you want to modularise your code  you might want to modularise your DB schema - e g   this is the  customers  area  this is the  orders  area  and this is the  products  area  and use join link tables between them  even if they re 1 n relations  and maybe duplicate the important information  i e   duplicate the product name  code  price into your order details table   Read up on normalisation  Someone else will recommend exactly the opposite for some or all of the above  p - never one true way to do some things eh

User · Answer

I really liked this article   http   www codeproject com Articles 359654 important-database-designing-rules-which-I-fo

User · Answer

Head First SQL is a great introduction

User · Answer

Experience counts for a lot  but in terms of table design you can learn a lot from how ORMs like Hibernate and Grails operate to see why they do things  In addition    Keep different types of data separate - don t store addresses in your order table  link to an address in a separate addresses table  for example  I personally like having an integer or long surrogate key on each table  that holds data  not those that link different tables together  e g   m n relationships  that is the primary key  I also like having a created and modified timestamp column  Ensure that every column that you do  where column   val  in any query has an index  Maybe not the most perfect index in the world for the data type  but at least an index  Set up your foreign keys  Also set up ON DELETE and ON MODIFY rules where relevant  to either cascade or set null  depending on your object structure  so you only need to delete once at the  head  of your object tree  and all that object s sub-objects get removed automatically   If you want to modularise your code  you might want to modularise your DB schema - e g   this is the  customers  area  this is the  orders  area  and this is the  products  area  and use join link tables between them  even if they re 1 n relations  and maybe duplicate the important information  i e   duplicate the product name  code  price into your order details table   Read up on normalisation  Someone else will recommend exactly the opposite for some or all of the above  p - never one true way to do some things eh

User · Answer

It s been a while since I read it  so  I m not sure how much of it is still relevant   but my recollection is that Joe Celko s SQL for Smarties book provides a lot of info on  writing elegant  effective  and efficient queries

User · Answer

These are questions which  in my opionion  requires different knowledge from different domains    You just can t know in advance  which  tables to build  you have to know the problem you have to solve and design the schema accordingly  This is a mix of database design decision and your database vendor custom capabilities  ie  you should check the documentation of your  r dbms and eventually learn some  tips  amp  tricks  for scaling   also the configuration of your dbms is crucial for scaling  replication  data partitioning and so on   again  almost every rdbms comes with a particular  dialect  of the SQL language  so if you want efficient queries you have to learn that particular dialect --btw  much probably write elegant query which are also efficient is a big deal  elegance and efficiency are frequently conflicting goals--   That said  maybe you want to read some books  personally I ve used this book in my datbase university course  and found a decent one  but I ve not read other books in this field  so my advice is to check out for some good books in database design

User · Answer

Head First SQL is a great introduction

User · Answer

It s been a while since I read it  so  I m not sure how much of it is still relevant   but my recollection is that Joe Celko s SQL for Smarties book provides a lot of info on  writing elegant  effective  and efficient queries

User · Answer

These are questions which  in my opionion  requires different knowledge from different domains    You just can t know in advance  which  tables to build  you have to know the problem you have to solve and design the schema accordingly  This is a mix of database design decision and your database vendor custom capabilities  ie  you should check the documentation of your  r dbms and eventually learn some  tips  amp  tricks  for scaling   also the configuration of your dbms is crucial for scaling  replication  data partitioning and so on   again  almost every rdbms comes with a particular  dialect  of the SQL language  so if you want efficient queries you have to learn that particular dialect --btw  much probably write elegant query which are also efficient is a big deal  elegance and efficiency are frequently conflicting goals--   That said  maybe you want to read some books  personally I ve used this book in my datbase university course  and found a decent one  but I ve not read other books in this field  so my advice is to check out for some good books in database design

User · Answer

I started out with this article  http   en tekstenuitleg net articles software database-design-tutorial intro html  It s pretty concise compared to reading an entire book and it explains the basics of database design  normalization  types of relationships  very well

User · Answer

These are questions which  in my opionion  requires different knowledge from different domains    You just can t know in advance  which  tables to build  you have to know the problem you have to solve and design the schema accordingly  This is a mix of database design decision and your database vendor custom capabilities  ie  you should check the documentation of your  r dbms and eventually learn some  tips  amp  tricks  for scaling   also the configuration of your dbms is crucial for scaling  replication  data partitioning and so on   again  almost every rdbms comes with a particular  dialect  of the SQL language  so if you want efficient queries you have to learn that particular dialect --btw  much probably write elegant query which are also efficient is a big deal  elegance and efficiency are frequently conflicting goals--   That said  maybe you want to read some books  personally I ve used this book in my datbase university course  and found a decent one  but I ve not read other books in this field  so my advice is to check out for some good books in database design

[sql] A beginner's guide to SQL database design

Examples related to sql

Examples related to database

Examples related to database-design

Examples related to scalability