Are my tables linked correctly in sqlite?

Question

Python

Greetings,

1. Question
I'll clarify right away that I'm not an expert in the database, and at this stage I just need to find a working solution.
There is a users table that contains data about the user, the user can have a photo with a face or just a photo with a 'cat'. If the photo shows a face, then the photo data should be saved to the faces table; if the photo shows something else, then the photo data is saved to the photos table, each user has only one photo.

Here is how at this stage I implemented it, I will be glad if you fix it.

CREATE TABLE IF NOT EXISTS users(id_faces INTEGER  NOT NULL, 
                id_photos INTEGER NOT NULL, 
                registration_date TEXT, 
                actual_name TEXT, 
                real_name TEXT, 
                old_names TEXT, 
                friends INT, 
                level INT, 
                country TEXT, 
                profile_summary TEXT, 
                src TEXT)
      
      
      
CREATE TABLE IF NOT EXISTS faces(id_faces INTEGER, 
                 h1 INT, 
                 FOREIGN KEY (id_faces) REFERENCES users (id_faces))


CREATE TABLE IF NOT EXISTS photos(id_photos INTEGER, 
                  h1 INT, 
                  FOREIGN KEY (id_photos) REFERENCES users (id_photos))

2. Question
in python, I get a number (actually an array of numbers) in this format:
6.1000e+01
How can I save it easier in the sqlite base (without rounding) and then get it from the base for comparison? Can I have an example?

Answer 1

1. It is better not to separate tables with photos into 2 different ones. Judging by the description of the task, the structure and the data stored in them are absolutely identical in essence. So it's better to use a single table to store all these photo options. And for separation (face / cats / perhaps another option you want to add), use a separate field in the table, photo_type for example.
2. In the presented code, you have FK from the table of photos to the table of users. If the photo table involves storing the data itself (what is the h1 field? is this the photo data itself?), then it’s more correct to do the opposite: in the user table, store the id from the photo table, with the corresponding FK.
Those. more or less like this

CREATE TABLE IF NOT EXISTS users(user_id INTEGER  NOT NULL PRIMARY KEY, 
                photo_id INTEGER NOT NULL, 
                registration_date TEXT, 
                actual_name TEXT, 
                real_name TEXT, 
                old_names TEXT, 
                friends INT, 
                level INT, 
                country TEXT, 
                profile_summary TEXT, 
                src TEXT,
FOREIGN KEY (photo_id) REFERENCES photos(id))

CREATE TABLE IF NOT EXISTS photo_type(photo_type INTEGER PRIMARY KEY,
                  photo_type_name TEXT)

CREATE TABLE IF NOT EXISTS photos(id INTEGER PRIMARY KEY AUTOINCREMENT,
                  photo_type INTEGER,
                  h1 INT,
FOREIGN KEY (photo_type) REFERENCES photo_type(photo_type))

3. By data format. It can be stored as INT, FLOAT, as far as I know, NUMERIC is also possible there, it's like an analogue of a decimal number.

6.1000e+01

Are you sure you have decimal numbers? The given example is in fact the integer 61.

an array of 256 non-integer numbers in the base (picture histogram)

As far as I know, sqlite does not support arrays as such. To store, you will have to put each number on a separate line in order (id autoincrement, in principle, can handle this). It is not a fact that this is the best option for storing this data, because in fact, as far as I understand, you will never need to select a specific number from this histogram, it is always needed only as an array - we write it completely, we read it completely.
So for such a task, it is probably better not to produce them in a table, but simply to create a text field and store this list of numbers there as json. In sqlite, you can even write automatic data transformation, i.e. a list is written to the column, and saved as json, similarly when reading. Example .

Answer 2

Numbers can be stored as a string or float
Both have a limit of 1 billion like or a million. Van, that should be enough.