Hello Guys !!
Back to the project. The Next Stage of Software/Application Developement is Designing. So Let’s start with First Stage Database Designing.
Which Database to use ?
Our scope of system is limited and database like MySql can be comfortable to be used for Online Database and Local Database like SqLite, Realm we are going to use SQLITE DATABASE.
Which are the tables and columns required for project ?
Firstly, To keep info of users of our system.we are going to create USER TABLE.
- To enter system for admin, he/she will login via login page. so we need to add entry at user table.
- To enter our system users like buyers and sellers will be using first registration and then login. We will keep buyers and seller personal info like name,age, verification number and username and password for login in USER TABLE.
- So our table USER TABLE columns will be:
- user_id: auto generated id : int value.
- user_name: users full name: varchar value(20)
- user_state: users living state: varchar value(25)
- user_city: users living city: varchar value (25)
- user_mobno: users mobile number: For now we will store as varchar(10) value, we can use bigint .
- user_verifyno: users aadhar card verification number – can have number and letters :varchar(12) value
- user_username: users username for login :varchar(10) value
- user_password: users password for login: varchar(15) value
- user_acverify: flag to allow users for login.
- user_type: type of user he is admin,seller or buyer.
Secondly, Admin is going to control the parameter like city, state, and grain quantity should be selled on this system. So, we will require STATE_CITY TABLE and to keep quantity limit and grains allowed ,we will have GRAINS TABLE.
- STATE_CITY TABLE columns:
- sc_state: state entry permitted by admin: varchar(25) value
- sc_city: city entry permitted by admin: varchar(25) value
- GRAINS TABLE columns :
- g_name: grain entry permitted by admin: varchar(20) value
- g_quantity: grain quantity in kg permitted by admin: int value
Thirdly, The most important table is MARKETPLACE where selling and buying of grains is going take place.
- MARKETPLACE TABLE columns:
- mkt_id: auto generated id: int value
- slr_id: seller id when he put his grains on sell : int value
- buyr_id:buyer id when buyer buys grains: int value other-wise will be empty.
- mkt_grainname: seller grains name on sale: varchar(20) value
- mkt_grainprice: seller grains name on sale: int value
- mkt_grainquantity: seller grains quantity on sale: int value
Lastly, just to get how to use local database on android phones, we are going to store order acknowledgment which will are going to receive as notification into database and show as in notification corner.
- notify_id : auto generated id: int value
- notify_msg : only text message: varchar(30)
- notify_timestamp: only text timestamp to show when message arrived : varchar (15)
Questionaries /Queries can be asked to Database:
- What if user forgets the password ?
- Taking verification number and username as details from user.
- Verifying details into USER TABLE .
- We will request new password and set it.
- How do I retieve all buyers who have bought grains from Seller B as his id=222 ?
- Using the MARKETPLACE TABLE ,we would query :
- select all records and apply condition where seller id=222 and buyers id not empty
*** TRY THESE TYPE OF QUERIES ,SO CAN GET MORE CLEAR SCHEME OF YOUR DATABASE ***
That’s it For Now . Next we are going to start implementation with designing screens keeping database in mind.