No matter what your level of expertise, it always helps to learn new tips and best practices from other IT professionals.  These tips are for new users and those just beginning to work with SQL Databases and tables.

​

​

1. Take The Time To Design Each Of Your Tables

   • Forecast what it is you’ll be using this table for. Devise a naming system that you’ll adhere to for all your column names. (eg. firstSecond or First_Second). Prepare for expansion and try to keep your layout as clean and concise as you can.

2. One Query Instead of Two

   • In most cases (I’m not sure about exceptions, so I’m using the word “most” on purpose), it’s always best to use the least amount of queries that you need. More queries means more work on the backend which, when compounded, might lead to a server running like molasses.

3. Join Tables To Make One Statement

   • Since we’re aiming for as little queries as we can, learn how to use the JOIN command to join tables with column values that can be linked together. It’s a little daunting at first, but once you get the hang of it, you’ll use it often.

4. Validate Data Before

   • We simply can’t rely on the web visitors to enter each and every form field correctly. You should make sure most forms have enough validation and if needed, develop some sort of CAPTCHA system to deter spammers. Data that goes in clean will lead to less headaches later on.

5. Deter SQL Injection

   • In short, SQL Injection is a attack where the malicious user inputs data into your form with the intent of skewing your SQL query. Protect yourself by blocking or encoding characters that have the potential to be used maliciously. Apostrophes are the biggest culprit in my experience.

6. SELECT What You Need

   • You should be avoiding using “SELECT *” in your queries unless you’re using each data column after the query. Selecting specific columns will help speed up your SQL queries.

7. Use More Tables

   • Don’t be afraid to use more tables. Sometimes, data is better off stored in another table, and referenced by another using a unique identifier. This can help keep your data clean and easier to expand.

8. You Should Have a Unique Field

   • Uniques fields are great to have in the event that you don’t have a difference between two rows of data. Sometimes, you’ll need to delete a row and you’ll have problems if it doesn’t have any unique data.

9. Bit Data Type

   • Its is not uncommon to experience problems where one is  able to insert “True” and “False” into a “bit” column on one server, but not on the other. Be sure to get into the habit of using 1 for True, and 0 for False or you might find yourself having to rewrite a lot of code.

10. Specify Lengths

    • When you specify your column’s data type, be sure to specify a length (if applicable). For example: varchar(5). I’ll be honest, I’m not sure why, but I imagine there is some benefit or else you wouldn’t have the option.

​