Database implementation

For our proyect we needed several tables. Each of them are explained ahead:

The table Users, described, looks like this:

The description of the items table, looks like this:

• Notes:
  • The image is a binary, because is the most functional way for sending an image through the server. So its simpler to record it as a binary file.
  • There can be items without a description, image or quantity.

• Notes:
  • The latitude and longitude will tell the exact location of the store, meanwhile the location will be used in a more user-friendly way of knowing which store it is, It will contain the name of the place where the store is located.

The description of the table Lists_users looks like this:

• Note:
  • The username is a Foreign Key, therefore there can't exist a record of a list of an user that isn't already in the table Users in the username field.
  • The listname is not the way the database identifies each list, it's only a way for the user to recognize it. Therefore, there can be lists for different users with the same name. This could happen with the same user, but for avoiding conflicts and misunderstandings with lists for the same user, the server will control that the same user doesn't create two lists with the same name.

• Notes:
  • In here, the list_id is a way of identify the list, through the table lists_users, and therefore there can't be a list_id that doesn't belong to a user in the table Lists_users.
  • Also, there can't be a item in a list, that isn't already in the items table.

• Notes:
  • As before, the barcode and the store_id are foreign keys to the Items and Stores tables;

This is the list of all the methods: It's important to note that, if a parameter has the same name in two or more tables, then we are talking about the same exact parameter

• Users table: All the user's information is managed here. These methods use parameters such as username, password, email, etc.
  • Retrieving the password of an user
  • Registering a new user
  • Changing the privileges of a user
  • Changing the password of an user
  • Retrieving the privileges of an user
  • Retrieving the email of an user
  • Adding a submit to an user
  • Retrieving the times an user has submitted something

• Items table: Similar to the previous one, but for the items. Some parameters are barcode, name, description and image, for example.
  • Registering a new item
  • Modifying an item
  • Retrieving the information of an item

• Stores table: All data about stores, with the locations being the most important. storeid, latitude, longitude and name are important parameters.
  • Registering a new store
  • Modifying a store
  • Retrieving the coordinates from a store
  • Retrieving the stores inside a given radius

• Lists_users table: This is the relation between the user and the lists he has created. Because of this, the only parameters are username, listid and listname.
  • Creating a new list for an user
  • Retrieving the lists of an user
  • Retrieving the list id
  • Deleting a list (also affects Lists_items)

• Lists_items table: This one specifies which items contains each list. Parameters for it's methods are listid (which is the same listid in the previous table) and barcode.
  • Adding an item to a list
  • Deleting an item from a list
  • Retrieving all items on a list

• Items_stores table: Finally, this methods manage the items contained in each store, so only here you will see the parameter price, besides barcode and storeid.
  • Adding an item to a store
  • Retrieving the lowest price of an item
  • Retrieving the different prices for an item and its corresponding store
  • Retrieving the average price of an item
  • Retrieving the lowest price of an item between certain coordinates (Due to a radius)

We're going to describe some of the most important methods we created for managing the Way_To_Shop database. These are many methods on our project, we're going to show the ones we consider the most representative of all, as they use most of the database actions, for example retrieving inserting and updating data.

• Registering a new user: Each time an user uses the suscribe button, it's data is stored in the Users table with this method.

def write_user(username, password, name, email):
	try:
		result = cursor.execute("INSERT INTO 'Users' ('username', 'password',  'name', 'mail') VALUES ( '%s' , '%s', '%s', '%s');" % (username, password , name,  email))
		db.commit()
	except: result = 0
	return result

• Adding a submit to an user: A submit is a way for the administrators to know how much activity an user has, so this method adds a submit each time the user registers or updates a store or an item. Once the number of submits reaches a certain amount (not defined yet), the privileges of the user are automatically aumented. When a new user is registered, it's privileges are automatically set to one.

def add_submit(username):
	try:
		result = cursor.execute("UPDATE Users SET submits = submits + 1 WHERE username = '%s';" % username)
		db.commit()
	except: result = 0
	return result

• Changing the privileges of an user: This is what happens when the number of submits reaches certain amount.

def modify_privileges(username, privileges):
	result = cursor.execute("UPDATE Users SET privileges=%s WHERE username = '%s';" % (privileges, username))
	db.commit()
	return result

• Registering a new item: If the user encounters an item that is not in tha database, he can submit it by sending the data indicated by the method.

def write_item(barcode, name, brand, quantity, description, image):
	try:
		result= cursor.execute("INSERT INTO Items (barcode, name, brand, quantity, description, image) VALUES (%s, '%s', '%s', %s, '%s', '%s'); " % (barcode, name, brand, quantity, description, image))
		db.commit()
	except: result = 0
	return result

• Retrieving the coordinates from a store: The application can tell the user where exactly the location of the store is by entering the id.

def get_coordinates(storeid):
	cursor.execute("SELECT latitude, longitude FROM Stores WHERE storeid= %s;" % storeid)
	return cursor.fetchone()

• Retrieving the stores inside a given radius: This is useful for knowing how many shopping options one has according to the current location.

def get_stores(latitude, longitude, radius):
	cursor.execute("SELECT storeid, location, name FROM Stores WHERE (latitude > %s) AND (latitude < %s) AND (longitude > %s) AND (longitude < %s);" % ((latitude-radius), (latitude+radius), (longitude-radius), (longitude+radius)))
	return cursor.fetchall()

Note: As you can see, this is a little different from the ones we've seen. What we are doing is limiting latitude and longitude according to the radius. The limits are: latitude-radius < latitude < latitude + radius, and longitude-radius < longitude < longitude + radius

• Deleting a list: If the user believes one of his lists is no longer useful, he can delete it. He will be prompted with a confirmation message before deleting it permamently.

def delete_list(listid):
	try:
		result1 = cursor.execute("DELETE FROM Lists_items WHERE listid = %s;" % (listid))
		result2 = cursor.execute("DELETE FROM Lists_users WHERE listid = %s;" % (listid))
		result = [result1,result2]
		db.commit()
	except: result = [0,0]
	return result

Note: This is a method that affects two tables, so we execute the cursor for both, and define the final result as a list of two results.

• Retrieving the lowest price of an item: This is one of the most useful functions of the application. It displays the lowest price of certain item on all the stores of the database.

  
def get_lowest_price(barcode):
	cursor.execute("SELECT storeid, price FROM Items_stores ORDER BY price ASC WHERE barcode = %s;" % barcode)
	return cursor.fetchone()

Note: We use the ORDER BY and ASC statements to put the lowest price as first, and then just fetch that one with fetchone().

• Retrieving the lowest price of an item between certain coordinates (Due to a radius): This is an extension of the previous method. If you don't want to travel far, you can view the lowest price inside a radius you establish.

  
def get_nearlowestprice(barcode, latitude, longitude, radius):
	cursor.execute ("SELECT price FROM Items_stores ORDER BY price LIMIT 1 WHERE barcode = %s AND (latitude > %s) AND (latitude < %s) AND (longitude > %s) AND (longitude < %s);"%(barcode, latitude-radius, latitude+radius, longitude-radius, longitude+radius))
	return cursor.fetchone()

UP
Back To Project