What is the most optimal way to do this? - In TV Show page I need to get all progress from all episodes in that TV Show - In Homepage I need to get the continue_watching - In Episode I need to get progress of the episode. - Some TV Shows have more than 100 episodes Ex:

USER_ID | SHOW_ID | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | 12           | ---

USER_ID | EPISODE_ID | PROGRESS
10      | 10         | 15
10      | 11         | 20
10      | 12         | 95

OR

USER_ID | SHOW_ID | PROGRESS                     | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | {"10":15, "11":20, "12": 95} | 12           | ---

In PosgreSQL I can get the individual progress with: SELECT progress->'10' as progress... I think that the best method is the first but it will create a lot of rows and this could make DB slower?

My first post on DBA! Apologies in advance if I made any mistake. Before jumping into the schema and tables, I would like to share what I am trying to achieve first. I am working on sort of a courier application, where I have some categories and each category has a pre-defined price. But determining the price is a bit ugly (absence of symmetry and patterns; at least, I can't seem to find any) . I will give you an example: Consider the following categories: Document, Heavy Document, Laptop, Carton, Heavy Carton. 1) **Document:** It's for the lighter documents, which are below 0.5kg. The price is 20$, fixed. *[price stored in the prices table: 20.00]* > e.g. For an item of 300g, the price will be 20$. 2) **Heavy Document:** This is for the documents that are over 0.5kg. Unlike the Document category, it doesn't have a fixed price! Rather, it has a unit price: 10$ per kg, which will be applied to each kg exceeding 0.5kg. *[price stored in the prices table: 10.00]* > e.g. For an item of 2kg, the price will be 35$ (1.5g = 15$ + 0.5 = 20$) 3) **Laptop:** Straightforward, 100$. Nothing special about it, no constraint whatsoever. *[price stored in the prices table: 100.00]* > e.g. For an item of 2kg, the price will be 35$ (1.5g = 15$ + 0.5 = 20$) 4) **Carton:** Here comes an interesting one. Until now, there was only one dependency: weight. But this one has an additional dependency: dimension. This is somewhat similar to the Document category. For the cartons that are below 3 Cubic Feet(CF), the price is 80$ per CF. The difference between Document and Carton category is that the Document has a fixed price, whereas Carton has a Unit Price. But wait, there's more. There is an additional constraint: dimension-weight ratio. In this case, it is 7kg per CF. And if the item's weight crosses the ratio, for each extra kg 5$ will be charged. It's so confusing, I know. An example might help: [price stored in the prices table: 80.00] > e.g. For a carton of 80kg and 2CF; the price will be 490$. Here is how: First calculate the regular charge: 80$*2CF = 160$ Now let's figure out if it crosses **Ratio**: Since, 1 CF = 7kg, hence, 2CF = 14kg. But the item's weight is 80kg, so it *crosses the ratio (14kg)* Since it crosses the ratio, for all the extra kgs (80-14 = 66kg), each kg will cost 5$: 66*5 = 330$. After adding it with regular charge: 330$+160$ = 490$. 5) **Heavy Carton:** This one is for the cartons having the dimension bigger than 3CF. The difference with Carton is the unit price. Heavy Carton is 60$ per CF. [price stored in the prices table: 60.00] > e.g. For a carton of 80kg and 5CF; the price will be 525$. Here is how: First calculate the regular charge: 60$*5CF = 300$ Now let's figure out if it crosses **Ratio**: Since, 1 CF = 7kg, hence, 5CF = 35kg. But the item's weight is 80kg, so it *crosses the ratio (35kg)* Since it crosses the ratio, for all the extra kgs (80-35 = 45kg), each kg will cost 5$: 45*5 = 225$. After adding it with regular charge: 300$+225$ = 325$. If you've read this far, I think I have convinced you that the business structure is really complicated. Now let's take a look at my categories schema: +-------------------------+---------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+---------------------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(191) | NO | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | | dim_dependency | tinyint(1) | NO | | NULL | | | weight_dependency | tinyint(1) | NO | | NULL | | | distance_dependency | tinyint(1) | NO | | NULL | | | dim_weight_ratio | varchar(191) | YES | | NULL | | | constraint_value | decimal(8,2) | YES | | NULL | | | constraint_on | enum('weight','dim') | YES | | NULL | | | size | enum('short','regular','large') | YES | | regular | | | over_ratio_price_per_kg | decimal(8,2) | YES | | NULL | | | deleted_at | timestamp | YES | | NULL | | +-------------------------+---------------------------------+------+-----+---------+----------------+ Also the schema of prices table (it's a polymorphic table, hoping to create a subcategories table someday): +----------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | amount | decimal(8,2) | NO | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | | priceable_type | varchar(191) | NO | MUL | NULL | | | priceable_id | bigint(20) unsigned | NO | | NULL | | | deleted_at | timestamp | YES | | NULL | | +----------------+---------------------+------+-----+---------+----------------+ How can I improve this structure to keep things as dynamic and coherent as possible?

Hello I need designing addresses table. I designed it but I didn't like it very much. My needs: - I have customers and users (admins). I have addresses for customers and users. We need design in different tables like CustomerAddresses and UserAddresses. But we must be have Addresses table and connect with CustomerAddreses and UserAddresses. - I have different address types like individual, corporate address. This types just using in customer's addresses. - A customer can have more than one address. - A user can have more than one address. - Customers are not individual or corporate. Customer addresses are individual or corporate. My design: (but I think this design not good. I think this design breaking normalization rules.) CustomerAddresses is junction table in this design. And belongs to CustomerAddressTypes. We can create a new design or update my design. https://dbdiagram.io/d/5fc2218b3a78976d7b7dbaf6

There is a SQL server, there are around 100 databases in it. I have to query a few tables from one of the databases. when I query, it's very slow and I think CPU utilization is very high at that time. I have also noticed that there are queries from other tables from other services which are affecting the overall performance of querying from the database. I am thinking to move these tables to a different database within the same SQL server. Do you think it will solve this issue? or it will not improve the performance of querying from my tables, I only bother about my tables. Will it have no impact because the new data will also be in the same SQL server? Please provide the answers in detail to my queries.

Currently I'm trying to build web app for online voting where registered user can create their own Election. For the database, every time user create an election should the system create new required table for the election or I could just some tables to hold everything ? 1. case 1 (create required table for each election ) : - user_table (id,name,email,etc) - election_table (user_id, election_id,etc) per election created by user above, below tables will be created in database with electionId is part of table name : - voter_electionId_table (voter_id,name,email,etc) - candidate_electionId_table (candidate_id,no,name) - ballot_electionId_table(vote_id,candidate_id) example expected result : 1. case 2 ( same tables for every election) : 2. user_table (id,name,email,etc) 3. election_table (user_id, election_id,etc) 4. voter_table(voter_id,election_id,etc) 5. candidate_table (electionID,candidate_id,no,name,etc) 6. ballot_table(electionId,vote_id,candidate_id) expected result : which from above is better ? or none of them is good (If yes, please share your thought).

I have an insert-only PostgreSQL database with a Book table, a Page table and an Element table. Books can have multiple pages and pages can have multiple elements. The database is insert-only because any time a page is modified, a new version of that page is created in a new record for auditing reasons. ------------- Book ------------- id [PK] ------------- Page ------------- id [PK] book_id [FK] version created ------------- Element ------------- id [PK] page_id [FK] version created Currently, Element has a Foreign Key pointing to Page.id and Page has a Foreign Key pointing to Book.id. A requirement is to have independent page drafts (versions) that can contain any number of elements. Every time a new page draft (version) is created, the new draft should be a clone of the previous version and it should contain all elements in the previous version. Elements can be added to or removed from a specific version of the Page, although Elements records are never deleted. For scalibility reasons, I would avoid cloning all the elements together with the page, since the elements could be a very large number. I would also what to avoid many-to-many relationships between pages and elements. I was thinking about replacing the Element.page_id FK with an Element.book_id FK so to have a single bucket of elements and pages more loosely coupled with each other. This way, when cloning a page, I wouldn't have to clone all the elements, but I would need a way to associate each element with a particular version of a Page. Ideally, I should be able to retrieve a page version with all the elements associated to that version with a single SQL query. I am still in a design/prototyping phase, so any aspect of the databse can potentially be changed to fulfill the requirements. What could be the best way to associate Pages and Elements according to the requirements above? I hope the requirements are clear, if not, please ask any questions and I'll try to clarify.

I am curious about "best" model for my purpose of storage of data (include historical changes) in Azure DB that I will be able to see timeline of that changes. Example: We have some company which making products. These products changing prices or description very often, sometimes they got deleted and next day they publish them again (with some changes). My app checking that company every hour and check all products which they have. Save them to Azure DB (doesn't be save in the same entity as company have them, just very short version):

- ID, 
- date, 
- price, 
- description, 
- few links for pics, 
- location.

Sometimes they update price so I check it again and if I see that record from DB have different price than on company shop. Or they change description, or etc. - I would like to store that change of. (Include previous value and new value.) - Include when it happen (date and hour, nothing too specific). I was thinking db scheme like: But I can say that: - I will have like 10 fields. - Checking thousands products every hour. So I feel bad that in every small change I store whole Product record again. Also it can happen that one product can be removed, and Add under different IdOfProduct (this is generated just by sequence on company side), but with the same location and description but for example different price. So I am also trying way how to store that products (even historical one, which are deleted) that if I will have "new product" that I can make fast compare with database if there is something similar (check location, after that check description, maybe even price) and If its similar, make a record to another table that there is possibility of the connection to the old product which was deleted). Can anyone give me advice how to Store these information that I will not waste the resources (db size or cpu resources on checking linked products from some messy db design)? Thank you. Every hint will help me :)

Lets assume we have an employee table as below **employee** id (PK) | name | date_of_birth | age | created_at We have a scenario where we need to add some metadata for each employee and not every employee will have this metadata, so we create a new table as below **employee_metadata** id (PK) | employee_id (PK) | ... | created_at There is a 1-1 mapping between both the tables What would be a better design practice for the primary key of employee_metadata table, 1 or 2? 1) A separate auto-incrementing id 2) Using employee_id of employee table

I am modelling a User table which needs to have the following information about the users: - is_active? - is_detained? - has_voluntarily_deactivated? - is_temporarily_suspended? and so on... Basically, these are boolean flags with true or false. So, I am considering few approaches other than boolean flags which are as follows: 1. Create a single varchar field with values like 'active', 'detained', 'deactivated', 'suspended', etc. 2. Create a tinyint field and map the integers to another table containing status strings 3. Create a tinyint field and map the integers in code itself using constants, such as ACTIVE = 1, DETAINED = 2, etc. Is Python's enum type the best solution to this? 4. Create a tinyint field and map the integers to status strings in an XML or JSON file Which of the above 4 or the original boolean style approach is preferable, or if there could be a completely different approach or a modified version of the above approaches, please let me know? Also, in my code, how should I call these fields, like: - if (user.status == 1), or something like - if (user.status == STATUS.ACTIVE), or - if (user.status == 'active') (I think this will depend on which approach I follow) These status values are not limited and may be added, edited or removed in future. Request you to answer in a database agnostic way and the programming language that I am using is Python. Thank you for your answers

I am not a database guru. But here's the matter with which I'd like some help. I have a relatively freshly-minted server running AlmaLinux 8.7, with 64GB RAM and a pair of terabyte SSDs. After updating to MariaDB 10.6.x, I configured the my.cnf file to represent a prior server with a similar setup: # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION bind-address = 127.0.0.1 # skip-name-resolve=1 query_cache_size=0 query_cache_type=0 local-infile=0 innodb_buffer_pool_size=1024M query_cache_size=64M performance_schema=ON tmp_table_size=96M max_heap_table_size=96M unix_socket=OFF table_definition_cache=2400 key_buffer_size=1024M innodb_buffer_pool_size=3G innodb_log_file_size=750M max_connections=300 !includedir /etc/mysql/releem.conf.d Several days have passed, and I have run the MySQL Tuner. I'd like some guidance about what to change/add/remove form my.cnf based on these suggestions: We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See MySQL: Welcome (specially the conclusions at the bottom of the page). When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Variables to adjust: skip-name-resolve=1 query_cache_size (=0) query_cache_type (=0) query_cache_size (> 64M) join_buffer_size (> 256.0K, or always use indexes with JOINs) tmp_table_size (> 96M) max_heap_table_size (> 96M) table_definition_cache (2400) > 3113 or -1 (autosizing if supported) key_buffer_size (~ 195M) innodb_buffer_pool_size (>= 3.3G) if possible. NOTE: skip-name-resolve=1 produces a database error in our backup logs, which is why it is disabled. Thanks for any further assistance. I am obviously not a database guru.

I am trying to implement a ticket system, the numbers generated would be sequential. 4000, 4001, 4002, 4003 My idea was to have a table that relates to current raffle, and the ticket_no column is an auto increment column that resets when the foreign key changes. The composite keys are raf_id and ticket_no since the ticket no should not exist multiple times in same raffle Is this possible? |------------|-----------|---------| | raf_id | ticket_no | user_id | |------------|-----------|---------| | 1 | 4000 | 1 | | 1 | 4001 | 1 | | 1 | 4002 | 2 | | 1 | 4003 | 3 | | 2 | 4000 | 4 | | 2 | 4001 | 4 | | 2 | 4002 | 5 | | 2 | 4003 | 1 | |------------|-----------|---------|

I have **a table with 10~15 columns** (some varchar, some int, some double, and some date). **Table can contain tens of millions of records.** **User can filter from all columns using all filter operator** (equal, start with, contains, ends with, major, minor..) and setting multiple filters (ex. Filter for column1 and column2 and column5 and column 8) **I would like to implement a solution who optimize the search response having a good compromise for insert performance.** **Solution1**: Create an index for each column. I think is not the best solution because it will compromise the insert performance and not allow to cover all combination of filters. **Solution2**: Using an EAV support table who contains the column data allowed to filter. CREATE TABLE FlatTable ( TableId UNIQUEIDENTIFIER NOT NULL, Column1 VARCHAR(64), Column2 INTEGER, Column3 DATETIME, Column4 FLOAT, ... Column10 VARCHAR(512), CONSTRAINT PK_FlatTable PRIMARY KEY (TableId) ) CREATE TABLE EavTable ( EavTableId UNIQUEIDENTIFIER NOT NULL, TableId UNIQUEIDENTIFIER NOT NULL, PropertyName VARCHAR (512) NOT NULL, StringValue VARCHAR (512), StringValueReverse AS REVERSE(StringValue), StringValueFullText VARCHAR(MAX), NumericValue MONEY, DateValue DATETIME, CONSTRAINT PK_EavTable PRIMARY KEY (EavTableId), CONSTRAINT FK_EavTable FOREIGN KEY (TableId) REFERENCES FlatTable (TableId) ON DELETE CASCADE ) CREATE UNIQUE INDEX UX_EavTable ON EavTable (TableId, StringValue, PropertyName) CREATE UNIQUE INDEX UX2_EavTable ON EavTable (TableId, StringValueReverse, PropertyName) CREATE UNIQUE INDEX UX3_EavTable ON EavTable (TableId, NumericValue, PropertyName) CREATE UNIQUE INDEX UX4_EavTable ON EavTable (TableId, DateValue, PropertyName) CREATE FULLTEXT INDEX ON EavTable(StringValueFullText) KEY INDEX PK_EavTable WITH STOPLIST = OFF; **Remarks**: *StringValueReverse* column is used to filter string values with "ends with operator" allow to resolve the query with a like 'reverseValue%' filter and perform and index seek on index UX2_EavTable. *StringValueFullText* column contains string value splitted on multiple words allow to use fulltext index on some circumstances when filter operator is "contains". *NumericValue* column contains the numeric value of the property if it is a number allow to perform and index seek on index UX3_EavTable *DateValue* column contains the datetime value of the property if it is a date allownto perform and index seek on index UX4_EavTable **With this schema i can filter on multiple columns using multiple EXISTS conditions with INDEX SEEK on all filters** (except some case filtering with contains filter) For example: SELECT * FROM FlatTable WHERE EXISTS (SELECT * FROM EavTable WHERE TableId = FlatTable.TableId AND PropertyName = 'Property1' AND StringValue LIKE 'value%' ) AND EXISTS (SELECT * FROM EavTable WHERE TableId = FlatTable.TableId AND PropertyName = 'Property2' AND StringValueReverse LIKE 'eulav%' ) AND EXISTS (SELECT * FROM EavTable WHERE TableId = FlatTable.TableId AND PropertyName = 'Property3' AND CONTAINS(StringValueFullText, '"value*'") ) AND EXISTS (SELECT * FROM EavTable WHERE TableId = FlatTable.TableId AND PropertyName = 'Property4' AND NumericValue > 100 ) AND EXISTS (SELECT * FROM EavTable WHERE TableId = FlatTable.TableId AND PropertyName = 'Property5' AND DateValue > '20240101' ) How you evaluate this solution? There is a better one? Thanks

I am planning to build a database that will store sensor value for various type of sensors in a factory building. Is there any best practice on how to do this? - Option 1 : should I integrate all sensors into a single database like this Table Sensor --- SensorID SensorType SensorDesc Table SensorReadings --- SensorReadingID SensorID ReadingDateTime ReadingValue - Option 2 : Or should I put each sensor type into separate table? Table TemperatureSensor --- SensorID SensorDesc Table TemperatureSensorReadings --- SensorReadingID SensorID ReadingDateTime ReadingValue (double) Table MotionSensor --- SensorID SensorDesc Table MotionSensorReadings --- SensorReadingID SensorID ReadingDateTime ReadingValue (integer) Considering that various types of sensor tables will have similar fields but potentially different reading data types (some of them are integer and some of them are doubles, etc). Is it logical to separate the tables for each sensors? Or keep using a single table for every type of sensors? - Which one is the best practice to implement such database if there might be various types of sensors to be added in the future? - Which one can be better optimized for performance? Thank you

I'm building a reporting solution. The data will be stored on Oracle database. I expect to get near to several billion data set since i have to keep data for 1 year period. When designing database schema i faced problem to go with single table or set of tables. 1. Single table in format of (ID, DATETTIME, JSON ) which has all the data dumped as json in JSON column. this will avoid any joins also cater for future event format changes. 2. Set of tables ( 4-6) data dived on them. query will be using several joins and several unions. Format will be hard to change. What would be the better approach in terms of performance. For large data set how efficient json over joins ?

I am fairly new to the DB design. I need to handle what a user can do on a specific resource. One of the main queries I need to drive is: > Get all the cars a user has write permission to. I think if I only had the one resource it would be easier, but I will have plenty of resources and so I am trying to design something via a ResourceType model, so that I don't have to create a lot tables as the number of resources grow. I saw this question: Discretionary Access Control (DAC) model For what I can tell that is the closest thing to what I am trying to accomplish because it differentiates between roles approach and discretionary. I do not have roles and won't have roles for now. When a user creates a resource (like Car, Location, etc...) the server assigns the "owner" permission for that user. The owner can in turn grant permissions to other users, like read, write... I have created the following tables: CREATE TABLE ResourceType ( Id (PK) Name resourceId ... ) CREATE TABLE Car ( Id (PK) model resourceTypeId (FK to car ResourceType) ... ) CREATE TABLE User ( Id (PK) Name ... ) CREATE TABLE Permission ( Id (PK) canRead canWrite ... ) then I created a junction table: CREATE TABLE ResourceTypeToUserPermission ( Id (PK) permissionId (FK to Permission) resourceTypeId (FK to ResourceType) userId (FK to User) ) I was hoping to use that junction table to get what I needed with inner joins and where clauses on userId = x... I am obviously not doing this right and I am wondering how I can modify things correctly so that for any resource I will have in the DB I will be able to get all resources of a given type with a certain type of permission for a given userId. Any help would be much appreciated.

In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative partitioning for largest tables. Tables partitioned by forecast identifier. Partition creating and dropping is made "on the fly" when users create or delete forecasts. However, I doubt if creating partitions in this way is good idea. **EDITED.** We do not use creating partition by BEFORE INSERT trigger. We create or drop section on backend after user start creating or deleting forecast on frontend of our web-application. **EDITED 2.** Backend in our web-application is web-server, that works with PostgreSQL 12 database.

I may be misunderstanding something basic here, but I'm struggling to find this issue being explained in my research. Let's say we have a users table and other tables that relate to the user, let's call them: - orders (contains a userId) - reviews (contains a userId) - vaccinations (contains a userId) Each user can have many orders, or reviews, or vaccinations. Now let's say for whatever code I'm writing I want to get all users, all their orders, reviews and vaccinations. **Should I be writing one query that left joins everything together, or three separate queries?** I.E should it be something like: SELECT * FROM users LEFT JOIN orders ON orders.userId = users.id LEFT JOIN reviews ON reviews.userId = users.id LEFT JOIN vaccinations ON vaccinations.userId = users.id Or three completely separate queries like: 1. SELECT * FROM users LEFT JOIN orders ON orders.userId = users.id 2. SELECT * FROM users LEFT JOIN reviews ON reviews.userId = users.id 3. SELECT * FROM users LEFT JOIN vaccinations ON vaccinations.userId = users.id ## Some background ## I think what's causing me confusion is that most my time spent querying SQL is using the node ORM Sequelize. It allows me to happily query the database using a single query that on the face of it makes sense. Something like this: return models.users.findAll({ include: [{ model: models.orders required: false }, { model: models.reviews, required: false }, { model: models.vaccinations, required: false }], }); In code it returns the results to me in a really nice ordered way that makes a lot of sense. However, what I realised when looking at the MySQL 'slow query' log is that some of these joins were returning hundreds of thousands of results per query. I guess this is due to how one extra row in one of the tables means the query then returns many more results. Just to repeat the question to end with **Should I be writing one query that left joins everything together, or three separate queries?** Thank you so much for your help.

I am working on a checklist app for inspection sheets for users on our shop floor. It's a paper process currently and they want to digitize it. I am using Postgres SQL for the database. I started out just making tables as I go but then they told me the number of inspections is over 100 so I'm hoping to find a more efficient way to do things. to start, we have different units that inspections fall under. For example, 1621 is a unit and we have I think 19 units. Within each unit there are at least 15 inspection sheets (assembly, roof enclosure, generator assembly, etc) and they have a different number of questions in each one. There is some overlap to the the inspections per unit (what I mean is most of the units have a generator assembly, roof assembly, and some others) but the questions for the inspection can/are be different per unit. I started by making 6 tables for 3 of the inspections before they told me about the units portion of the project. So I had a table with the questions for an inspection and a table for the submissions. I really don't want to go about it this way but if that's the best option I will stick with it. Can anyone guide me towards some already created schema's I can build off of or resources to read/watch/etc that may help?

I am currently designing a mobile application that sends push notifications to users subscribed to a set of topics. The list of possible topics changes infrequently (not more than once per year), while users are frequently added or removed, and their subscription list changes frequently. I have researched both relational databases and key/value databases, and am struggling to determine the best type of database for this problem. I have several parameters/restrictions on the design of the database: 1. Given any

, quickly retrieve the list of topics that a user is subscribed to 2. Given any

, quickly retrieve the list of all users subscribed to that topic 3. Allow persistence to disk as a single file to allow easy network transfer across machines, but cross-platform compatibility is not required as the file will only be accessed across homogenous machines. 4. Preference is given to proven and robust solutions; but if using a prebuilt solution, it must be free or open source (working with a strict budget) 5. Can use as much disk space as needed, but must not require excessive amounts of CPU or RAM 6. Built-in replication capability and consistency assurance is preferred, but not required 7. macOS solutions are preferred, Linux-only solutions are permitted, Windows-only solutions are discouraged My thinking is that since there is both an association between users and topics, I need two key/value tables as in the following schema:

UserTable  = {userID : [topic]}
TopicTable = {topicID: [user]}

where

{:}

denotes a hash map (such as

::map

in C++),

[...]

denotes a dynamic-length array (such as

::vector

in C++). Note that

is a pointer to specific

(userID, topic)

key-value pair in

, and

is a pointer to a specific

(topicID, user)

key-value pair in

. This is a data structure I was able to implement in C++ successfully, and this satisfies requirements (1), (2), (5), and (7). Unfortunately, there doesn't seem to be an industry-standard method of efficiently serializing pointers to disk, which seemingly make (3) and (6) impossible. The cereal or boost C++ libraries have methods for this purpose, but I don't know if they are designed for real-time performance, or if they could be considered to satisfy (4). All of these issues make me think that perhaps I need to rethink my database schema, but then I'm back to having issues with (1) and (2). I have thought of several ways of doing so. One would be to store the data as JSON and serialize directly to disk with a fast and compact binary representation such as BSON, but this would seem to involve an excessive amount of I/O operations as the table grows larger, and reduce performance due to the increased number of cache misses and page loads required. I could use a relational database such as Python's

, which seems to meet all of the requirements, but my lack of background in databases makes me unable to determine how I would make this data fit the relational model (would the primary key be

, or

? How could I ensure fast lookups by either key, when a user could potentially subscribe to 100 different topics, or a topic might have 100,000 users? Surely I wouldn't make one column for each of the ~10,000 possible topics, when it is rare for a user to subscribe to more than 10?) I've also considered Erlang's

as it is one of the only databases I have found besides Python's

which is tightly coupled with a general-purpose programming language, and it seems to support (6) nicely. Unfortunately, I haven't been able to find any clear information about the performance of

or Erlang in general. **Important Note** I understand many users may suggest I benchmark each of these options. Unfortunately, since I am new to database management, even developing a benchmark for each of these options will require a significant investment into learning the associated frameworks. This is also a startup company whose mobile application has not yet been released, so any benchmark is likely a poor representation of real-world traffic. As with any new app, most likely user onboarding will be a slow and gradual process, but I want to avoid what would amount to an unintentional DoS attack if the app goes "viral" and garners a large number of downloads quickly. This is why the numbers given above are loose estimates of a worst-case scenario (with the exception of possible topics, which is guaranteed to be ~10k for the foreseeable future), and why I am unable to perform adequate benchmarking as user activity remains a significant unknown variable that cannot be eliminated prior to launch. **EDIT** @FrankHeikens mentioned in comments that my requirement (3) is highly restrictive. I originally sought a single-file model due to the ease of backing up or transferring such a database; however, if replication and backup capabilities are included in a solution this requirement can be relaxed.

I'm currently building a quiz app and I need to design a database schema for it. This is what I've tried so far: The biggest problem I have is with how to represent which questions have been already answered correctly by each user and which haven't. In the current design the *answered_questions* table would grow very fast (assuming a pool of 2000 questions and 1million users it would be 2 billion entries, which doesn't seem to be a scalable approach). I thought of partitioning the *answered_questions* table by *user_id* but I don't if that's the most optimal solution. Please note that I need to keep the information about which question is already answered because for each request i will have to retrieve the following information: * 5 questions that have been correctly answered * 5 questions that have not been correctly answered filtered by the *category*, *subcategory* and *user_id*. How could I redesign it to avoid having to query the *answered_questions* table which can become very large over time? Any help is very much appreciated.