Android - How to create and combine two tables within a list of Sqlite tables on Android?

Asked

Viewed 373 times

1

I’m creating an app to monitor aquarium settings using the following schema:

inserir a descrição da imagem aqui

The user can monitor as many aquariums as he wants, but every time he adds a new tank, these two tables should be created and always linked.

And the water parameters need to be maintained over time, because I’m going to use them in a graph. Showing the changes over time. For example, a chart showing the amount of salt over a week.

That’s where my doubt comes in, because I’m just starting to program.

  1. How can I create and join these two tables every time the user adds a new aquarium?
  2. And how I would do not mix with other tables, if the user had added 5 aquariums in the app?

I’ve been doing some research and seen what foreign key might be useful in my case, but I don’t know if it would help me, or how to proceed.

Note: Just a clarification. I do not need the be-a-bá, I have a basic knowledge on Android. I know how to handle a db, do CRUD operations, Cursor, Contentprovider, etc. But I am lost in this case. I would not like to pass the impression of laziness. Just no sense of which way to go, or what tools to use.

android sqlite

  • Why two different tables? Can an aquarium have more than one parameter and/or vice versa? I believe not, and the best solution would be to unite both tables. If you want to research more about cardinality in the relations between tables

  • I’m not sure I understand your question, but I’ll try to clarify. Two tables why the information from the Quario itself does not change, but the parameters do. For example, the salinity of the water changes over time, the pH of the water can change very quickly over the course of the hours, or days. The user may very well want to record the parameters several times lasting the day, or the week. So I got the idea of having a table exclusively for the paramenters. But if you can still do it in one table, what would it be like? I want to keep data over time to display a chart

  • So the relation between aquarium and parameters and 1:N, because you always want to keep track of previous parameters, it would not be interesting to add a date field?

  • I want to keep the record of the data to display a graph. I think I do this using date and time. I think I have now managed to explain it better.

1 answer

0

You need to add a new column in the table parameters, this column will be a foreign key that references the primary key of the aquarium table (aquarioId):

CREATE TABLE parametros(
    //...
    aquerioId INT NOT NULL,
    FOREIGN KEY(aquerioId) REFERENCES aquerio(aquerioId)
);

Whenever you add a new row in the table parameters, you should add the aquarium id that row refers to

Note that the foreign key must be the same data type and size as the primary key it refers to

  • I get it. So in this case I could have several tables Quario and only one with the parameters, right? For example, this would happen if the user had 5 aquariums. Right?

  • More or less, yes the user can have several aquariums, but the way I indicated each tank can have several parameters, I understood that this is what I wanted, many parameters for each aquarium, I understood right?

  • Yes. It is, but only one other question remains. Each time the user wants to add a new Quario, will I have to create two new tables? Or I can keep creating the only Aquario table and using only 1 parameter table to keep saving the parameters of all aquariums?

  • No, in this template you do not need to add a parameter every time you add an aquarium, you can add only the aquariums and when the user wants to add the parameters, but a parameter cannot be linked to more than one aquarium, if you want this you will need another table, in that bet I’ll explain this better. However, from what I understood of the project, I suggest that it continues as it is, two tables an aquarium has none or many parameters that are of only one aquarium

Browser other questions tagged android sqlite

You are not signed in. Login or sign up in order to post.