All Questions
Tagged with sqlite database-design
71 questions
3
votes
3
answers
509
views
Table structure with multiple foreign keys and values
I'm developing a desktop application to help the technician to easily provide updates to an embedded system.
The background is very simple the database (SQLite) have several tables, not identical but ...
- 143
0
votes
1
answer
85
views
Create an affiliate system (also known as a referral program like Rewardful, Tapfiliate) in SQLite?
I want to create a simple affiliate system that pays affiliates who refer a customer a commission of $100.
I currently have the following tables generated via an ORM.
CREATE TABLE `session` (
`id` ...
1
vote
0
answers
35
views
User types as foreign keys
Largely theoretical question or idea discussion
Lets say we have several simple dictionaries:
create table dic1(code integer primary key, description varchar(256));
create table dic2(code integer ...
- 984
2
votes
1
answer
78
views
Database design suggestion for improving existing design
Background
I am working on an IoT application that deals with sensors, their configurations and their readings. Following are the requirements of the system,
Each sensor can support multiple features....
- 121
2
votes
0
answers
148
views
Is my food-tracker's database designed properly? (ER diagram + SQL Relations)
My idea:
The database will store foods, days, and "inserted foods" aka diet_logs (days and diet_logs have both existence dependency)
for each diet_log of a specific day, I will create a ...
- 21
2
votes
1
answer
1k
views
SQLite: Can you use a DELETE trigger to delete other records in the same table?
In this specific case I'm using a SQLite database, but I believe this to be more of a conceptual question with practical implications.
I have a nested-set model in my database and I would like to ...
- 123
-1
votes
1
answer
1k
views
How to query likes and user details for posts?
I'm pretty new to the world of databases and SQL.
I'm trying to build a system for users to submit posts and like posts. I've been looking at a few similar questions on this site, reading about third ...
- 101
0
votes
1
answer
90
views
How do I record an application session duration in SQLite?
Objective: Log the session duration of the user on the application.
I have a session_log table:
CREATE TABLE IF NOT EXISTS "session_log"
(
"id" INTEGER PRIMARY KEY,
...
1
vote
0
answers
433
views
How to log changed table fields in SQLite?
I have a table that looks like this
CREATE TABLE IF NOT EXISTS "ebooks"
(
"id" INTEGER PRIMARY KEY,
"name" TEXT NOT NULL,
"author"...
0
votes
0
answers
106
views
Is this database correctly structured?
I am in the process of converting a bus timetable to a database structure. I am building a free mobile app for a specific itinerary in my country that will:
let users know when their next bus is due ...
- 101
0
votes
1
answer
623
views
How to design & implement folders and subfolders (infinite) in my database?
So I am building a desktop application using Qt, C++, and SQLite.
I want the user to be able to create as many folders as he likes, and those folders can have "notes" (its a note-taking app) ...
0
votes
1
answer
45
views
Best way to arrange a hairdresser database based on their skills
I need some help in a smart way to organize a database for a hairdresser shop.
So far, I understand that I need a table for the hairdressers containing usual data such as id, name, etc.
I also think I ...
- 101
-1
votes
3
answers
127
views
LEFT JOIN Clause
I think it would be better for me to just ask why it is that we use the LEFT JOIN clause for the following task?
List all manufacturer name and phone number that have poor sales… less than 3 sales of ...
0
votes
2
answers
73
views
Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014
We are given 5 tables.
Orders
Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)
Customers
ID ...
1
vote
1
answer
38
views
Create a DELETE statement that deletes Manufacturers with less than 10 sales
Orders:
Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)
Customers:
ID (Primary Key)
Name
Phone Number
Street (ie. ...
0
votes
1
answer
43
views
How do I store World/event data in a treelike structure SQL/SQLite3
I would like to create a database which contains tables of the following form, where each record in a table, is a table in and of itself. Ideally at any one time I would be able to query any table of ...
1
vote
3
answers
3k
views
What is the best way to store product size and color for an Ecommerce database?
I am trying to find the best way to store product size, colour for an sports store website, something similar to what amazon does, where you can choose a shoe colour and number. The problem is that a ...
- 125
0
votes
1
answer
49
views
Multiple values for a single column [closed]
I've got a small database with 5 tables: 3 of them are resources (material, skill and tool) and the other 2 are tasks (corrective maintenance and preventive maintenance).
Every table has a primary key ...
- 1
0
votes
0
answers
102
views
Relational DB which can do inserts at billions of rows with multiple indexed columns?
I am looking for advice on a database to potentially replace SQLite for my application.
To briefly describe my DB requirements:
needs to support billions of rows and the most queried table has ...
0
votes
1
answer
42
views
How to add an additional table over two one to many relations tables?
I have two tables TableA and TableB which have a relation of one to many
Later I am told that there must be an additional table that links the initial tables with additional information. To carry ...
- 103
-1
votes
1
answer
346
views
How should cardinality be understood in a table with a compound key?
I am learning about database structuring and when watching the following video I cannot identify if there is a cardinality between the ENVIO, PEDIDO and PRODUCTO tables?
If it is clear to me that ...
- 103
0
votes
1
answer
62
views
copy a sqlite3 table preserving custom _id
I'm trying to copy a table whose _id field is given by integers with some gaps, e.g. 1,2,5,7, presumably due to deleted messages.
(This is a toy-model for a Whatsapp database I'm manipulating.)
I'd ...
- 103
0
votes
1
answer
288
views
How can I fill in a foreign key value after creating the first entry of the table the foreign key is referencing?
I am seeking to create an online store so I got to the database definition. I want to have as few tables as possible, so I've got them narrowed down to:
Users
Products
Orders
Cart_Items
I got to the ...
- 3
1
vote
1
answer
5k
views
Import csv file into sqlite3 table - ERROR "Insert failed: datatype mismatch"
I am using sqlite3 and I am writing my scripts in two files. The first one is called "Books.sql"
DROP TABLE IF EXISTS Books;
CREATE TABLE Books (
books_id INTEGER PRIMARY KEY AUTOINCREMENT,
...
- 13
1
vote
1
answer
1k
views
Storing many pandas DataFrames in SQLite with metadata
I need some help with designing a database. My aim is to persistently store a number of pandas DataFrames in a searchable way, and from what I've read SQLite is perfect for this task.
Each DataFrame ...
- 113
1
vote
1
answer
14k
views
FOREIGN KEY Constraint Failed: SQlite3
I have two tables, created as below,
sqlite> CREATE TABLE Artists(
...> ArtistId INTEGER PRIMARY KEY,
...> ArtistName TEXT NOT NULL
...> );
sqlite> CREATE TABLE Albums(
...&...
- 213
0
votes
2
answers
171
views
Database design question: Tags with values(duration) linked to them
I am creating a database that takes in daily inputs such as:
Date
Events in that date (e.g. project foo: 5 hours, bar: 2 hours, etc...)
Rating of the date
There's more but these are probably the ...
1
vote
1
answer
55
views
Is there any advantage to putting some columns in another table, or should I just put them in the same table?
I have a SQLite database with about 50 tables in it. The largest table contains location data and it has 6,000,000 rows.
An example of some of the columns in this table are:
location_contrivedkey --(...
1
vote
2
answers
1k
views
Deduplication/normalization of string column on insert
I have a database of text tuples. Imagine e.g. full file paths + standard comments. Then you dump large file tree and generate comments for files. A file can have lots of comments, but their text ...
- 123
0
votes
1
answer
128
views
How to deal with refrencing a foreign key to two tables?
I have an interesting situation where I am making a data base off someone else's design that has to be as rule complaint as possible.
The issue I have is with the section shown above as it is a sub-...
- 1
2
votes
1
answer
2k
views
How to design a parent-child relationship that includes child priority/order
I am trying to design a table with a string column in SQLite3, I believe this is necessary to connect the tables parent to child, which represent entity types that are involved in a connection with a ...
0
votes
1
answer
51
views
How to multi-match two tables efficiently?
I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both ...
- 101
1
vote
2
answers
119
views
Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries
I am facing the following problem:
I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country ...
- 11
0
votes
1
answer
615
views
SQLite how to store translations
Im looking for advise because my experience is not really good in SQLite.
My table look like this:
CREATE TABLE IF NOT EXISTS Route (
id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL,
name ...
- 101
1
vote
0
answers
769
views
SQLite database: many-to-many optimization (simple but large example) without IDs available?
I'm a beginner looking for people who can think through these things more easily to see where I've messed up. I have a many-to-many table that I am trying to optimize for speed of creation, speed of ...
- 111
1
vote
1
answer
2k
views
Creating a database structure for a quiz
My app has this menu:
TestMode - make a quiz with 26 rndom questions from the database with a timer of 30 minutes.
LearningMode - make a quiz with all the questions from my db, displaying the correct ...
- 11
2
votes
1
answer
2k
views
Inventory database maintaining remaining qty of an item
In my inventory database there is a sale table.
This table contains fields sold qty and balance qty of an item after each sale.
While implementing 'edit sale' feature, I need to update the sold ...
2
votes
2
answers
128
views
Correct organization of the database structure and selection
Sorry for newbie question.
For example, there is some kind of abstract device, we will call the Device class and the tasks for it: Task1, Task2, Task3.
For concreteness, I will describe everything in ...
- 23
1
vote
1
answer
2k
views
Will Derby, H2, or SQLite give faster load time and/or smaller file size than HSQL?
I have some flat files with the following columns; 3 integers, 3 reals, and 1 varchar(20). For querying I need an index that contains both 1 of the integer columns and the varchar column. Each file ...
- 723
2
votes
1
answer
828
views
Design / Normalization of a Database
I’ve got a question concering the design of a SQLite database. I’m building a database for analyzing purposes that constists of works of literature and metadata concering those entities.
The thing I’m ...
- 121
1
vote
0
answers
523
views
Attendance and payroll management design
I am developing an Android app for the first time. For the database I am using SQLite . I am new to the concept of relational database. I am creating an app for attendance and payroll management. I am ...
- 11
4
votes
3
answers
654
views
If the query for database too complicated, does it means the design of Database is bad?
I am new to database, and below is one of my query with it's explanation, I feel it's too complex. It works, but I am worried the design is bad.
Provide a list of customer information for customers ...
- 163
2
votes
2
answers
850
views
Problem with compare value with its aggregate function within the same table
I need to find the list of customer_id for customer who have spent more than the average customer.
This is the the query I use to generate table above:
SELECT customer_id, sum(Total) AS ...
- 163
0
votes
4
answers
678
views
Database design to record occurences in tv show
I want to make a database where I record the guests of each episodes of a tv show. I want to be able to do the following stastics:
What guest had the most appearences?
Which two (or three) guests ...
- 177
3
votes
2
answers
140
views
Using floats for PKs. Is it a good idea?
Let's say I have an entity PizzaStore which has a location as part of its attributes
Now a PizzaStore can have N of other PizzaStore that are close by.
So seems to be a self referencing relationship 1-...
- 771
1
vote
1
answer
525
views
Questions and answers database design for Core Data [closed]
I am designing database for a question and answer projects , and I am stuck. I do not know how to do it.
Here is information:
There will be many users
There will be many questions
Each question has ...
- 11
2
votes
2
answers
5k
views
Bus timetable Database structure
I am creating a database for bus timetable in android app. I have created four tables: stops, route, detail_route and schedule. columns in each table are as follows.
Database structure
route (id, ...
- 123
2
votes
0
answers
106
views
Is this user-storage database system efficient, effective and secure?
I am using:
PHP Version 5.2.17-MIT-BACKPORT-GnuTLS
SQLite 2.8.17
I have a database called users, which currently has a table called root. There will be others, once I get all the rows for root ...
- 143
2
votes
2
answers
1k
views
sqlite database table with 1000's of columns
I am using sqlite3 in an application to conduct an alalysis of distances between sets of points. Distances between these points have been precomputed using dijkstra's algorithim and left me with a ...
- 123
0
votes
1
answer
2k
views
Derived classes with same base class and class specific ids
I have three classes in my project. One base class and two derived classes.
public class Animal
{
public long Id { get; set; }
public DateTime Created { get; set; }
public string Name { ...
- 101