Design DB

 Database Design concepts
*Tables names should not be plural
 column should meaning full
*Try natural key to use as primary key maximum , minimize surrogate key.
*Natural key can be found from business and should be finalized for future
* Use code for meta and master tables primary key, if not must use code unique and foreign key for other tables
foreign key column establishes a direct relationship with a primary key or unique key column (referenced key) usually in another table:
*Use naming convention for master and transaction tables
mst_
txn_ or _txn
*Define constraint

unique, index, check ....

*MySql  - Relatoional Database, using foreign key with primary key and unique key .

*Don't forget to focus on data quality and integrity - do not duplicate same data everywahre

( You shouldn't have tables designed to store the same information in multiple locations)

Use
triggers ,views....

write parameterized queries in the application - views may use for that

Some important points for the MySql Database

1.       1.Use ASC or DESC when you are using ‘LIMIT’ in the Query, Sometimes you may not get desired results

2.      2. Distinct not work only for selected column, but eliminates duplicate rows,

Ex :

nativeQuery.append("SELECT ");

nativeQuery.append("DISTINCT(payment_reference_no.id),");  

nativeQuery.append("(\\@cnt \\:\\= \\@cnt \\+ 1) AS rowNumber, ");

nativeQuery.append("payment_reference_no.created_at AS  prnCreatedAt, ");

3.     3.  Use always parametrised query [Already explained by Prageeth]

4.   4.    Need, full group by columns after the use aggregate functions[depends on where clause]

5.     5.  IF foreign key is not null ,use JOIN[inner join] not the LEFT JOIN,…