Apps Searching and Storing Data

[likwidmonster]

I am looking for recommendations on how to have the data setup for a car wash appointment scheduler app I am making for fun.

I think there is a better way to store and search through the information with the way I have it setup below.

So far this is what I have,

On an online MYSQL Database:

User Table: name, street, zipcode, state, phone, username, password

Vehicles Table: username, year, make, model, color

On the App:

SharedPreferences with name, username, street, zipcode, state, phonenumber, password

SQLiteDatabase with year, make, model and color.

When the user logins on the main activity it checks for the username and password in the User Table on the online server. If there is a match it downloads the full user data. It also downoads all the vehicles that have the correct username from the Vehicles Table. Then this data is stored in the app in its proper place (user data in the Shared Preferences and the vehicles each entered into the SQLiteDatabase).

What I am having trouble implementing is a full history of service per vehicle. I think there is a more efficient way of getting the vehicle history without searching through an appointments of every users appointments every time I want to show the vehicles history.

Any recommendations?

EDIT: The only other idea I have is to create a table on the server and the local SQLite that is the named "username-vehicle-appointments". That way it has to search through a smaller table and there is less data to download.

I am still trying to think a way to have the appointment request and confirmation setup.

EDIT:

Maybe have a new table on the server that is named new-appointments. The app could check the new-appointments table every few minutes to see if there are new appointment requests?

Does this sound like a good way to do this?

Thanks!

 

[Thom]

Welcome to Android Forums.

This thread was moved to this forum that is dedicated to discussing development subjects.

... Thom

 

[Deleted User]

Firstly, tables in a database normally have a primary key (PK), which is usually an integer value, and would auto-increment when you add a new record. So the first record gets a PK of 1, and the second gets 2, etc..

Secondly, relationships between tables are achieved using foreign keys. The keys would be the primary keys of the tables.Using foreign keys ensures referential integrity, so the database prevents you deleting a record from one table, which is referenced by another.

Another point is that you may want to consider the relationship between the User and Vehicle tables. Can there be many Users associated with a single vehicle? Can many vehicles be associated with a single user?
Complex relationships between tables such as many-to-many will require a link (association) table.
You could simplify the database schema by limiting the possibilities of how the records relate to each other, but it might compromise the functionality of your app.

You almost certainly will require an Appointments table if you wish to store the service record of all vehicles. This would have a foreign key reference into the Vehicle table.
A vehicle history would be found by searching for all records in the Appointments table associated with a given Vehicle.

So there's a bit to think about here, and your best plan may be to sketch out the table relationships (schema) before you start coding. There are database schema design tools available. Even with a relatively simple app, it's a good idea to get a clear picture, as changing things later may be tricky. You want to have maximum flexibility in your design.