Blog

Common mistakes made while designing database

I have met plenty of students who come for guidance or seeking trainings on various databases. Database is hugely popular amongst the student community and its expertise is much coveted.

During such encounters, I have come to observe a few basic myths or a few mistakes done by students while designing databases, which prove expensive & dangerous, later on. We usually discuss all of these in the first few days of our training. Here is just a subset. For generality, I'm using a typical college information database as a reference, since most students are acquainted with this one.

1.    The first step in database designing is finding entities – This one is the most prevalent myth, even amongst seasoned professionals, but still remains a MYTH. If database was about entities, we could have well named it as entity base. Isn’t it? So, if it is named as database, it has to begin with data. Data that will act as a base for the application to run, data that will help to deal with all functions of saving, editing, retrieving. Data that will be effectively used to generate reports, analyze & for Business Intelligence. After all, that is what all applications do, at various levels of complexity.

So, when beginning a database, focus on data. Try to get data about the system from as many sources as possible and as many ways as possible through forms, manuals, existing systems & the like. Don’t go after entities. They would come much later.

2.    Let us decide the attributes of an entity now, the data types can be decided later

OR

We know names would be type string or varchar, the length can be decided later – LATER spells disaster for database design. If you decide to keep anything for later, it sure would NEVER be done. And we all know how applications without a good, solid database, fare.

Do not keep anything for the later stages what has to be done at the ‘Design’ phase because, this data would be used when you are writing code, specifically, the functions/methods that will do all processing. You will use this data to put ‘checks’ in the code. You will use this data to test. So if the data is itself not defined correctly, how can you test the application against it?

Let me illustrate it with a scenario. We were working on enhancement of an existing employee payroll system for a client, when we faced a problem. The client wanted to allow for codes along with actual phone nos. & also wanted to separate them with hyphen ‘-‘. The previous software company that made the application in the first place, had defined the phone nos. as int type & length 10.  So when it happened that we began to store nos. with codes, we got errors, since the length exceeded 10 digits with codes & hyphen ‘-‘. We had to make changes in ALL the forms using phone nos.  Which meant –

  • Changes in tables
  • Changes in get, set functions
  • Changes in functions where these were taken as input
  • Changes in functions where these were stored into database

So, when deciding types, think how the real world uses the type & what would be convenient for the user. Don’t just focus on getting the task done immediately; keep the future in perspective too.

3.    We have a table 'student' with several (obvious) attributes. So, we'll name them as name, age, etc. We also have a table 'teacher'. Its attributes would also be named as name, age, etc. Naming is sadly, the most neglected part, which if carried out correctly, could do so much to create a database that is intuitive and can be accessed uniformly. Adopt a nomenclature & use it throughout the design. I agree it is boring, tedious, cumbersome & tempting to let go of, every now & then. But it is WORTH the effort.

Rule 1. Use it throughout.

For e.g. There are many ways to name tables. Students like underscores though. If you like them, use them all along like – tbl_teacher, tbl_student, etc.

There are other ways too – using a mix of cases or a combination of both underscore & lower & upper cases. Usually, commercial database design uses these approaches when differentiating between master, transaction, relationship tables & the like.

Rule 2. Simplify

Long names are boring & time consuming. Keep them short but DOCUMENT the short forms so that every team member knows what the short names stand for.

For e.g. tblTchr, tblStdt, etc. are perfectly valid to use so that names of attributes are not too long. Like - tchrDateOfBirth, stdtFatherName, etc. Also, avoid too short names which can mean almost anything. tblSt for ‘student’ is simply out of question!

4.    We can keep name of ‘student’ of 25 characters & that of teacher as 30 characters.

No! Keep length of similar data types same as far as possible. Again, this would help two way – brings about uniformity & you can design common code for such data types.

For e.g. – If I have to design a function that checks whether studentName is of 25 characters, I can even use it to check teacherName if it is also of same length. The result; less code written & more testing achieved.

A lot written & a lot to be implemented. Sadly, much of the interest in the student community (that I've interacted with), is for learning specific databases, not in learning the essence of building or designing a database. Hope this tutorial triggers your interest. In our training on ‘Effective Database Design’, we try to divert some of the eagerness towards this area along with building their fundamentals on the same.

So, even if you begin with these, you would be on the way to getting skilled on your most favourite subject. DO keep these things in mind EVEN while working on academic projects. These will differentiate you from your fellow members & who knows might fetch you MORE marks!

Want more of such insights & trainings?

 

I have met plenty of students who come for guidance or seeking trainings on various databases. Database is hugely popular amongst the student community and its expertise is much coveted.

During such encounters, I have come to observe a few basic myths or a few mistakes done by students while designing databases, which prove expensive & dangerous, later on. We usually discuss all of these in the first few days of our training. Here is just a subset. For generality, I'm using a typical college information database as a reference, since most students are acquainted with this one.

- See more at: http://genpro.co.in/blog/2011/10/common-mistakes-made-while-designing-da...

I have met plenty of students who come for guidance or seeking trainings on various databases. Database is hugely popular amongst the student community and its expertise is much coveted.

During such encounters, I have come to observe a few basic myths or a few mistakes done by students while designing databases, which prove expensive & dangerous, later on. We usually discuss all of these in the first few days of our training. Here is just a subset. For generality, I'm using a typical college information database as a reference, since most students are acquainted with this one.

- See more at: http://genpro.co.in/blog/2011/10/common-mistakes-made-while-designing-da...

I have met plenty of students who come for guidance or seeking trainings on various databases. Database is hugely popular amongst the student community and its expertise is much coveted.

During such encounters, I have come to observe a few basic myths or a few mistakes done by students while designing databases, which prove expensive & dangerous, later on. We usually discuss all of these in the first few days of our training. Here is just a subset. For generality, I'm using a typical college information database as a reference, since most students are acquainted with this one.

1.    The first step in database designing is finding entities – This one is the most prevalent myth, even amongst seasoned professionals, but still remains a MYTH. If database was about entities, we could have well named it as entity base. Isn’t it? So, if it is named as database, it has to begin with data. Data that will act as a base for the application to run, data that will help to deal with all functions of saving, editing, retrieving. Data that will be effectively used to generate reports, analyze & for Business Intelligence. After all, that is what all applications do, at various levels of complexity.

So, when beginning a database, focus on data. Try to get data about the system from as many sources as possible and as many ways as possible through forms, manuals, existing systems & the like. Don’t go after entities. They would come much later.

2.    Let us decide the attributes of an entity now, the data types can be decided later

OR

We know names would be type string or varchar, the length can be decided later – LATER spells disaster for database design. If you decide to keep anything for later, it sure would NEVER be done. And we all know how applications without a good, solid database, fare.

Do not keep anything for the later stages what has to be done at the ‘Design’ phase because, this data would be used when you are writing code, specifically, the functions/methods that will do all processing. You will use this data to put ‘checks’ in the code. You will use this data to test. So if the data is itself not defined correctly, how can you test the application against it?

Let me illustrate it with a scenario. We were working on enhancement of an existing employee payroll system for a client, when we face

- See more at: http://genpro.co.in/blog/2011/10/common-mistakes-made-while-designing-da...

I have met plenty of students who come for guidance or seeking trainings on various databases. Database is hugely popular amongst the student community and its expertise is much coveted.

During such encounters, I have come to observe a few basic myths or a few mistakes done by students while designing databases, which prove expensive & dangerous, later on. We usually discuss all of these in the first few days of our training. Here is just a subset. For generality, I'm using a typical college information database as a reference, since most students are acquainted with this one.

1.    The first step in database designing is finding entities – This one is the most prevalent myth, even amongst seasoned professionals, but still remains a MYTH. If database was about entities, we could have well named it as entity base. Isn’t it? So, if it is named as database, it has to begin with data. Data that will act as a base for the application to run, data that will help to deal with all functions of saving, editing, retrieving. Data that will be effectively used to generate reports, analyze & for Business Intelligence. After all, that is what all applications do, at various levels of complexity.

So, when beginning a database, focus on data. Try to get data about the system from as many sources as possible and as many ways as possible through forms, manuals, existing systems & the like. Don’t go after entities. They would come much later.

2.    Let us decide the attributes of an entity now, the data types can be decided later

OR

We know names would be type string or varchar, the length can be decided later – LATER spells disaster for database design. If you decide to keep anything for later, it sure would NEVER be done. And we all know how applications without a good, solid database, fare.

Do not keep anything for the later stages what has to be done at the ‘Design’ phase because, this data would be used when you are writing code, specifically, the functions/methods that will do all processing. You will use this data to put ‘checks’ in the code. You will use this data to test. So if the data is itself not defined correctly, how can you test the application against it?

Let me illustrate it with a scenario. We were working on enhancement of an existing employee payroll system for a client, when we face

- See more at: http://genpro.co.in/blog/2011/10/common-mistakes-made-while-designing-da...

To be able to understand this tutorial, you should know how to use any of these components - jComboBox or jList or jTable. Reference database – Our good old college information system GUI components - Swing IDE - NetBeans (6.8.1 or higher) Many of you might not have even heard about data models. Let me assure you, they very much exist & are used widely. Even you can use them, without waiting to work on big projects. You add this learning to your small academic projects & make them big instead. Let us understand a data model through a sequence of steps.

  • Add any combo box onto a JPanel. Let's name it stdtCombo. By default, the data that you see in the stdtCombo is a list of items named ‘Item1’, ‘Item2’, etc.
  • Right click on the stdtCombo to navigate to its properties. You will find that the property field ‘model’ is the source of this data. This is nothing but your data model. Because this is the default model for the jCombBoxit is named 'DefaultComboBoxModel'.
  • Normally, if you want to replace this data with your own, say names, you would start replacing ‘Item1’ by ‘Akash’, ‘Item2’ by ‘Amit’ & so on,  in this field.     

But this model is static i.e. it will ALWAYS show these names; the data cannot be changed on any specific trigger or action at runtime. So, is there a way to create a dynamic custom data model? One that would show, say names of boys – ‘Akash', 'Amit', 'Anand’ on the click of a jButton1 labeled ‘Show boys in class’? Or show names of girls – ‘Raina', 'Reema', 'Riddhi’ on  click of another jButton2 labeled ‘Show girls in class’? Yes, there is. We are assuming that our database has various details of the student stored - name, gender, etc. & we can come to know the student’s gender & name by searching on the key studentRollNo in tblStudent.

  • Let’s assume we run a database query & get all students of ‘male’ gender from tblStudent. Just to illustrate, in MySQL it would be something like - SELECT studentName FROM tblStudent WHERE studentGender = 'MALE'; (Assuming studentGender is of type varchar).            
  • We add each of these names obtained in the resultset to an ArrayList object named boysList and return it to the Swing jPanel form.

A quick recap, before we move to the actual implementation. So, the jComboBox (& similar components) has some data model attached to it by default, called the DefaultComboBoxModel. But it is static, meaning the data model is not separated from the view. Every time I will use the combo it will have ONLY THIS data to show. But in any application, user might want that things be dynamic, i.e. the combo displays data given by the user at runtime. Whenever we, as users, want to customize existing functionality, we override default behaviour. So, we will override the DefaultComboBoxModel with our customized data model which we call the 'Custom Data Model'. All said, how do we do it.

  • Create an event on button click for jButton1. Add the below code in the event handler.
  • Instantiate the DefaultComboBoxModel as -

         DefaultComboBoxModel cbModel = new DefaultComboBoxModel(); (Do not forget to add import javax.swing.DefaultComboBoxModel)

  • Add each element of the boysList to cbModel through a loop.  (Refer APIs of ArrayList & DefaultComboBoxModel)

         for (int pos = 0; pos < boysList.size(); pos++) {                cbModel.addElement(boysList.get(pos));          }

  • Once you have added all elements, set this model for the stdtCombo -

         stdtCombo.setModel(cbModel );

Now, you have a 'Custom Data Model', which will show the names of all boys in the class on the click of jButton1. Similarly, you can display names of girls on click of jButton2.

Now, for some formal definitions -

What is a custom data model? A 'Custom Data Model' is a data model customized by the user. This is usually created to separate data from view i.e. not attaching ANY SPECIFIC data to the view component (the stdtCombo here). This is done by overriding the default data model with custom or user defined data model (in our case the cbModel). The user will decide with what data the combo should be populated. The source of this data can be an array, arraylist or any other structure. The 'Custom Data Model' will be populated with different data depending on user action/triggers, etc. so that on different triggers, the view component displays different data.

'Custom Data Model' offers flexibility of use. It definitely is a good thing to learn since real world applications seldom deal with static data. It might seem too much at the start, as with any concept in Java :). But do persist, if you are on the way to learning the REAL Java. And, use this tutorial to get that initial thrust!

- See more at: http://genpro.co.in/blog/2011/10/what-custom-data-model-how-use-it#sthas...

To be able to understand this tutorial, you should know how to use any of these components - jComboBox or jList or jTable. Reference database – Our good old college information system GUI components - Swing IDE - NetBeans (6.8.1 or higher) Many of you might not have even heard about data models. Let me assure you, they very much exist & are used widely. Even you can use them, without waiting to work on big projects. You add this learning to your small academic projects & make them big instead. Let us understand a data model through a sequence of steps.

  • Add any combo box onto a JPanel. Let's name it stdtCombo. By default, the data that you see in the stdtCombo is a list of items named ‘Item1’, ‘Item2’, etc.
  • Right click on the stdtCombo to navigate to its properties. You will find that the property field ‘model’ is the source of this data. This is nothing but your data model. Because this is the default model for the jCombBoxit is named 'DefaultComboBoxModel'.
  • Normally, if you want to replace this data with your own, say names, you would start replacing ‘Item1’ by ‘Akash’, ‘Item2’ by ‘Amit’ & so on,  in this field.     

But this model is static i.e. it will ALWAYS show these names; the data cannot be changed on any specific trigger or action at runtime. So, is there a way to create a dynamic custom data model? One that would show, say names of boys – ‘Akash', 'Amit', 'Anand’ on the click of a jButton1 labeled ‘Show boys in class’? Or show names of girls – ‘Raina', 'Reema', 'Riddhi’ on  click of another jButton2 labeled ‘Show girls in class’? Yes, there is. We are assuming that our database has various details of the student stored - name, gender, etc. & we can come to know the student’s gender & name by searching on the key studentRollNo in tblStudent.

  • Let’s assume we run a database query & get all students of ‘male’ gender from tblStudent. Just to illustrate, in MySQL it would be something like - SELECT studentName FROM tblStudent WHERE studentGender = 'MALE'; (Assuming studentGender is of type varchar).            
  • We add each of these names obtained in the resultset to an ArrayList object named boysList and return it to the Swing jPanel form.

A quick recap, before we move to the actual implementation. So, the jComboBox (& similar components) has some data model attached to it by default, called the DefaultComboBoxModel. But it is static, meaning the data model is not separated from the view. Every time I will use the combo it will have ONLY THIS data to show. But in any application, user might want that things be dynamic, i.e. the combo displays data given by the user at runtime. Whenever we, as users, want to customize existing functionality, we override default behaviour. So, we will override the DefaultComboBoxModel with our customized data model which we call the 'Custom Data Model'. All said, how do we do it.

  • Create an event on button click for jButton1. Add the below code in the event handler.
  • Instantiate the DefaultComboBoxModel as -

         DefaultComboBoxModel cbModel = new DefaultComboBoxModel(); (Do not forget to add import javax.swing.DefaultComboBoxModel)

  • Add each element of the boysList to cbModel through a loop.  (Refer APIs of ArrayList & DefaultComboBoxModel)

         for (int pos = 0; pos < boysList.size(); pos++) {                cbModel.addElement(boysList.get(pos));          }

  • Once you have added all elements, set this model for the stdtCombo -

         stdtCombo.setModel(cbModel );

Now, you have a 'Custom Data Model', which will show the names of all boys in the class on the click of jButton1. Similarly, you can display names of girls on click of jButton2.

Now, for some formal definitions -

What is a custom data model? A 'Custom Data Model' is a data model customized by the user. This is usually created to separate data from view i.e. not attaching ANY SPECIFIC data to the view component (the stdtCombo here). This is done by overriding the default data model with custom or user defined data model (in our case the cbModel). The user will decide with what data the combo should be populated. The source of this data can be an array, arraylist or any other structure. The 'Custom Data Model' will be populated with different data depending on user action/triggers, etc. so that on different triggers, the view component displays different data.

'Custom Data Model' offers flexibility of use. It definitely is a good thing to learn since real world applications seldom deal with static data. It might seem too much at the start, as with any concept in Java :). But do persist, if you are on the way to learning the REAL Java. And, use this tutorial to get that initial thrust!

- See more at: http://genpro.co.in/blog/2011/10/what-custom-data-model-how-use-it#sthas...

To be able to understand this tutorial, you should know how to use any of these components - jComboBox or jList or jTable. Reference database – Our good old college information system GUI components - Swing IDE - NetBeans (6.8.1 or higher) Many of you might not have even heard about data models. Let me assure you, they very much exist & are used widely. Even you can use them, without waiting to work on big projects. You add this learning to your small academic projects & make them big instead. Let us understand a data model through a sequence of steps.

  • Add any combo box onto a JPanel. Let's name it stdtCombo. By default, the data that you see in the stdtCombo is a list of items named ‘Item1’, ‘Item2’, etc.
  • Right click on the stdtCombo to navigate to its properties. You will find that the property field ‘model’ is the source of this data. This is nothing but your data model. Because this is the default model for the jCombBoxit is named 'DefaultComboBoxModel'.
  • Normally, if you want to replace this data with your own, say names, you would start replacing ‘Item1’ by ‘Akash’, ‘Item2’ by ‘Amit’ & so on,  in this field.     

But this model is static i.e. it will ALWAYS show these names; the data cannot be changed on any specific trigger or action at runtime. So, is there a way to create a dynamic custom data model? One that would show, say names of boys – ‘Akash', 'Amit', 'Anand’ on the click of a jButton1 labeled ‘Show boys in class’? Or show names of girls – ‘Raina', 'Reema', 'Riddhi’ on  click of another jButton2 labeled ‘Show girls in class’? Yes, there is. We are assuming that our database has various details of the student stored - name, gender, etc. & we can come to know the student’s gender & name by searching on the key studentRollNo in tblStudent.

  • Let’s assume we run a database query & get all students of ‘male’ gender from tblStudent. Just to illustrate, in MySQL it would be something like - SELECT studentName FROM tblStudent WHERE studentGender = 'MALE'; (Assuming studentGender is of type varchar).            
  • We add each of these names obtained in the resultset to an ArrayList object named boysList and return it to the Swing jPanel form.

A quick recap, before we move to the actual implementation. So, the jComboBox (& similar components) has some data model attached to it by default, called the DefaultComboBoxModel. But it is static, meaning the data model is not separated from the view. Every time I will use the combo it will have ONLY THIS data to show. But in any application, user might want that things be dynamic, i.e. the combo displays data given by the user at runtime. Whenever we, as users, want to customize existing functionality, we override default behaviour. So, we will override the DefaultComboBoxModel with our customized data model which we call the 'Custom Data Model'. All said, how do we do it.

  • Create an event on button click for jButton1. Add the below code in the event handler.
  • Instantiate the DefaultComboBoxModel as -

         DefaultComboBoxModel cbModel = new DefaultComboBoxModel(); (Do not forget to add import javax.swing.DefaultComboBoxModel)

  • Add each element of the boysList to cbModel through a loop.  (Refer APIs of ArrayList & DefaultComboBoxModel)

         for (int pos = 0; pos < boysList.size(); pos++) {                cbModel.addElement(boysList.get(pos));          }

  • Once you have added all elements, set this model for the stdtCombo -

         stdtCombo.setModel(cbModel );

Now, you have a 'Custom Data Model', which will show the names of all boys in the class on the click of jButton1. Similarly, you can display names of girls on click of jButton2.

Now, for some formal definitions -

What is a custom data model? A 'Custom Data Model' is a data model customized by the user. This is usually created to separate data from view i.e. not attaching ANY SPECIFIC data to the view component (the stdtCombo here). This is done by overriding the default data model with custom or user defined data model (in our case the cbModel). The user will decide with what data the combo should be populated. The source of this data can be an array, arraylist or any other structure. The 'Custom Data Model' will be populated with different data depending on user action/triggers, etc. so that on different triggers, the view component displays different data.

'Custom Data Model' offers flexibility of use. It definitely is a good thing to learn since real world applications seldom deal with static data. It might seem too much at the start, as with any concept in Java :). But do persist, if you are on the way to learning the REAL Java. And, use this tutorial to get that initial thrust!

- See more at: http://genpro.co.in/blog/2011/10/what-custom-data-model-how-use-it#sthas...

Contact Us

R-58, Zone 1 MP Nagar, Bhopal - 462011

Call - +91 755 407 6667

E-mail - contactus@genpro.co.in