Bazaar – php example code – part 2 – structure of database tables


Article focus on database sctructure design for Bazaar app. Proces of normalization and atomisation of user data is important for minimalisation of redundant data in database tables.

Normalisation of the database tables

For our first aproach to establisching database structure also known as describing database schema we need explain some terms.

Schema – or database schema is description of data (tables and columns) in database, along with related objects and connections among them (relations).

For interconnection of separate table are used relations created with pair primary keys and foreign keys.

For further readings about database relationship, please visit page.

Normalization – means designing database with reduction of duplicate data as posssible with separation data into tables with relations between them.

These are the steps leading to normalised database structure:

  • Question yourself: What is the main thing you want your table to be about?
  • List all information you need to know about one thing in approriate table.
  • Break down all information about that thing into pieces for further organizing the table.

Basic concept focus on term of the atomic data. Atomic data are data that has been broken down into the smallest form needed for a database.

For further readin about data normalization please visit wikipedia page here.

Schema of bazaar table

Our design is break down into a four table, bazaar_user, bazaar_itme and bazaar_category. Relations between them is shown on text picture.

Bazaar app database schema drawing created in Dia editor

For simplifieng database creation process we prepared our first createdatabase.php script as follows:

<!– *************************************************************** –>
<!– PHP  code for automation of preparation databasetable for bazaar app          –>
<!-**************************************************************** –>
<!– Vrsion: 1.0        Date: 10.10.2020 by                                   –>
<!-**************************************************************** –>

<?php // script for accessing database and first table structure establishement
require_once(‚appvars.php‘); // including variables for database

/* Attempt MySQL server connection. Assuming you are running MySQL
server with  (user ‚admin‘ with  password test*555) */
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PW, DB_NAME);
// Check connection
if($dbc === false){
    die(„ERROR: Could not connect to database. “ . mysqli_connect_error());
// Attempt create table query execution
$sql1 = „CREATE TABLE bazaar_user (
    username VARCHAR(40) NOT NULL,
    pass_word VARCHAR(44) NOT NULL,
    nickname VARCHAR(40) NOT NULL UNIQUE, /* not two identical nicknames allowed*/
    first_name VARCHAR(40) NOT NULL,
    lastname_name VARCHAR(40) NOT NULL,
    addresss VARCHAR(40) NOT NULL,
    city VARCHAR(40) NOT NULL,
    write_date DATETIME NOT NULL,
    email VARCHAR(70) NOT NULL , /* not UNIQUE e-mails because one user can submitt different benchmark results */
   /* message_text TEXT */ /* optionally add boolean fields for subscription */
    GDPR_accept BOOLEAN NOT NULL default 0, /* BOOLEAN value if user accepted GDPR */
    rules_accept BOOLEAN NOT NULL default 0, /* BOOLEAN value if user accepted portal rules */
    avatar  VARCHAR(70),                      /* link to image */
    profile_text TEXT                       /* submit text from publisher */

$sql2 = „CREATE TABLE bazaar_item (
    name_of_item VARCHAR(40) NOT NULL,
    price_eur VARCHAR(40) NOT NULL,
    subcategory_id INT NOT NULL,
    users_id INT NOT NULL,
    item_add_date DATETIME NOT NULL,
    published BOOLEAN NOT NULL default 0,
    screenshot1  VARCHAR(70),                      /* link to image of item 1 */
    screenshot2  VARCHAR(70),                      /* link to image of item 2 */
    screenshot3  VARCHAR(70),                      /* link to image of item 3 */
    item_description TEXT,                        /* item description */
    CONSTRAINT FK_subcategorz_id FOREIGN KEY (subcategory_id) REFERENCES bazaar_category(subcategory_id), /* foreign key N site of 1 to N relation */
    CONSTRAINT FK_users_id FOREIGN KEY (users_id)  REFERENCES bazaar_user(users_id) /* foreign key N site of 1 to N relation */


$sql3 = „CREATE TABLE bazaar_category (
    category VARCHAR(40) NOT NULL,
    subcategory VARCHAR(40) NOT NULL

echo „<h2>Processing database tables for bazaar app.</h2>“;

echo ‚ | ************************************************************ | ‚;
echo „<br>“;
echo ‚ |    PHP  code for automation of preparation databasetable for bazaar app       | ‚;
echo „<br>“;
echo ‚ | ****************************************************************** | ‚;
echo „<br>“;
echo ‚ | Vrsion: 1.0        Date: 10.10.2020 by                           | ‚;
echo „<br>“;
echo ‚ | ******************************************************************* | ‚;
echo „<br>“;

if(mysqli_query($dbc, $sql1)){
    echo „Table 1 – bazaar_user created successfully.“;
    echo „<br><br>“;

} else{
    echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);
    echo „<br><br>“;

if(mysqli_query($dbc, $sql3)){
    echo „Table 3 – bazaar_item created successfully.“;
    echo „<br><br>“;
} else{
    echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);
    echo „<br><br>“;

if(mysqli_query($dbc, $sql2)){
    echo „Table 2 – bazaar_category created successfully – as last table because foreign key references.“;
    echo „<br><br>“;
} else{
    echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc);
    echo „<br><br>“;
// Close connection

After succesfull run of this script, we obtained these output in phpmyadmin page:

Output of database creational script
Obtained database tables with appropriate fileds structures

Conclusion and final thoughts

Established database structure prepared our startingpoint for obtaining data from users. database was created with emphasis on normaisation and atomisation of stred data.

Full sourcecode of our project for further study and free adaptation can be obtained from github here.

Share the article via the network
Translate »