Tag: Database

database design – Using one sequence across all tables in PostgreSQL

I propose using a single sequence for the bigint identity columns of most or all tables. (Essentially, OID, except 64-bit.)

  1. This helps prevent security problems for a multi-tenant application that joins tables incorrectly.

  2. While a uuid could be used for this, bigint is half that size, helping save index space.

My understanding is that sequence generation is very fast in PostgreSQL.

The only downside I can see is widening the type of tables could get by with 4 byte integer IDs, but thanks to PostgreSQL upsert behavior (generating a new value whether or not it’s needed), I rarely use int IDs anyway.

Is there an issue with approach? I haven’t found anyone advocating for it, but I consider the join safeguard to be quite nice.

How can I access information stored in my database by the Ultimate Member Plugin?

The Ultimate Member plugin is a WordPress user registration plugin. The user can enter information into forms on the profile page. How does Ultimate Member store this information in the database and how can I access it so I can use it in other places in my website?

database – IOT data interpretation

This is a more architectural question and I am hoping to get more ideas out of this thread. I have a computer vision model detecting daily life activities and sending data to the server every second. I want to show that data on the frontend but only after interpreting it in a comprehensible way.

So the process currently is:

  1. Activities get detected
  2. Some data is pushed to my API
  3. Data is stored in the database

To interpret the data in comprehensible way we have to go back and look at previous rows to confirm what this data is. For example if the sleep was detected and is consistently detected for the past 1 hours then it was sleep otherwise it was not really sleep. To reach this result so far we have come up with a few architectural plan:

Option 1: Triggers – Upon inserting the data in the database we write a trigger that goes back and determine what kind of activity is this.

  • Pros: We figure out the activity immediately and can make that data available to the customers really soon.
  • Cons: Triggers usually slow things down. I am worried this will send the database performance or might result in deadlock somewhere. Given that we will be inserting data multiple times a second from different directions this can become a liability soon.

Option 2: Stored Procs – After database insert has happened a stored proc that contains all the logic separately goes through the data and classifies the activity.

  • Pros: Processing happens immediately.
  • Cons: Again any such functions on database can create overhead given the volume of data we will be dealing with.

Option 3: Add interpretation logic on the server – After data is push to the API before inserting we get all the previous rows and analyse that data on the server and only then insert it. So far this is looking the most viable option to me. I am using nodeJS so it seems easy enough to do that.

  • Pros: No DB overhead, immediate, server can handle more complex logic
  • Cons: It can possibly make the server slow if we do this on every request

Option 4: Have another service running that looks at the data every minute and classifies it and puts it in a separate table.

  • Pros: No DB overhead, hopefully won’t make the server slow.
  • Cons: Not immediate

I am here to find out if there are more obvious ways to handle this sort of thing that I don’t yet know about. If there are any obvious AWS services that I can use for this that would also be appreciated

Scheduled messages pushed from database to API

I’m building a system with a dataflow, as described below. It is reasonable, or can it be improved, especially I would like comments on the job that moves messages from the database to the queue.

Users can post messages with a timestamp in the future. When the timestamp is reached, the message is published to an API endpoint.
The schedule’s granularity is 5 minutes and delays of up to 10 minutes are not an issue. The volume is no more than 100 messages per scheduled timeslot.

When the users post the message, it is stored in a database.

Then a single recurring job polls the database every five minutes for messages that are ready to be published.
The messages are then published to a queue where multiple consumers can process the messages and publish them to the API. When messages are moved to the queue, they are marked as processing, and when the consumer has published to the API, they are marked as processed with the status code from the API.

Would you see the single job, moving from the database to queue as a bottleneck? or do you see that operation as fast enough to accommodate many many messages?

To scale the single job to multiple jobs is there another way than locking the table?

Best Way to hash passwords in database

We have a user table where we store the user passwords. Now what we do is that we have a static salt, that we append to plaintext password and do a SHA-256 of the complete text and store in DB.

We have created this approach 5 yrs back. But now with so much computing power, is it safe to use SHA-256(With the approach I mentioned).

Or should I use any other approach? like HMAC? or anything else

architecture – Database Design for Form Builder

Hi guys I’m trying to create Form builder in my app for different Events that have happened and publish them, so the users can fill them out.

Little About Logic:

Forms are created per different Event that has happened, and then users can popuplate and view them

As for the database I’m using Postgres.

Here is how my current models look:

Form model:

form
-----
  id (PK)
  name
  user_id (FK to users.id)

Form Element

form_element
-------------
  id (PK)
  form_id (FK to form.id)
  element_type_id (FK to element_type.id)

Element Type (could be text_field, radio, dropdown)

element_types
-------------
  id (PK)
  name

Element Values (Stores Values inputed by User)

Form Element value
===============
    id (PK)
    element_id (FK to form_elements.id)
    user_id (FK to user.id)
    value

The form is populated with form_elements that reference the forms. And has a relationship 1 to many (one form has many fields). element_types is used so they know which type they are. element_types will store a list of different elements a form can have. Types could be: “text_field”, “drop_down_list”, “radio_buttons”, “checkbox”.

Now I have few questions about my Database Design ?

  1. Form Element model and Element Type model should have 1 to 1 relationship ?
  2. How should I create for example type Dropdown where would I store additional fields for Dropdown options in a new field, in a new Table, how would I design that ?
  3. Element Values table should be Many to Many, should I have to store user_id per Field valoue or could i have somehow bind it to Form ?

Database Role based access control design for survey app

I’m going to design the RBAC for a survey app. Each survey has the same role as below and only 1 manager, 1 leader and multiple participants.

Role_ID|Role_Name  |
-------|-----------|
      1|admin      |
      2|manager    |
      3|leader     |
      4|participant|

Each user can have multiple roles and this user can choose to be the participant role to join one survey or not when he is the manager or leader of a survey. To make the user only can action the survey he owns, for example, UserA is assigned to be the leader of SurveyA, so he can only has the capability, edit_survey, on SurveyA but not another surveyB which he’s not assigned to, how should I design the database?

I generated two options, can someone check which one is better or there is another better solution?

Option1

I put the role manager and leader column as the FK of User_ID from User table on Survey table as it’s one to one relationship and create a new participant table for participants.

enter image description here

Option2

I create a new user_role_in_survey table to store manager and leader role and this table replaces old user_role table. Survey_ID is the FK of Survey table and participant table for participants.

enter image description here

network – How does bitcoin find the block locator hashes in the leveldb database?

I am trying to understand how bitcoin core builds the list of block locator hashes needed for sendheaders and getheaders messages. I have looked on the wiki and I have seen that after the 10 most recent block hashes, it goes back exponentially until the genesis block.

So it seems that I have to get a block hash knowing its index in the blockchain. But how can bitcoin core find a block hash by its index? From my understanding leveldb lets you query data only by a key, and in this case the key is what I want.

postgresql multicolumn partitioning – Database Administrators Stack Exchange

Postgres 11

I want to make a table calendars, that is at the same time partitioned by range (one month of data) and a list of keys.

The reason is that:

  1. I want to prune months that are not longer updated.
  2. I need all the data to be partitioned by list of ids for further joins with and other partitioned table listings, that is partitined by the same list of keys.

The documentation says:

Declarative partitioning only supports range, list and hash partitioning, whereas table inheritance allows data to be divided in a manner of the user’s choosing.

Thus, if I get it right, this means that my taks can not be done with Declarative partitioning, but probably can be done using Inheritance.

So, I try to reproduce the given example with my modification

CREATE TABLE measurement (
                city_id         int not null,
                logdate         date not null,
                peaktemp        int,
                unitsales       int
            ) PARTITION BY RANGE (logdate); # this already does not accept mixed types of partitioning

than I try to create a partition with my mixed rules of partitining:

CREATE TABLE measurement_y2006m02 (
                CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' 
                        AND paektamp >0 AND peaktemp <=10)
                      ) INHERITS (measurement);

this gives:

cannot inherit from partitioned table "measurement"

Thank you!

database design – Data modelling and openAPI3.0 for customer and license management – when to know how to split entities?

I’m working on a unique ERP + Ecommerce which is a mix of “Shopify meets Adobe Cloud admin”.
Current project: ancient php framework with MVC, no architecture, resembles a procedural website
Refactor objective: switch architecture to MVVM using OpenAPI to generate proxy classes

When redesigning with OpenAPI and doing database normalization
How entities should be separated from each other?

Entity:

Company
Account Owner
Account Member
License Ownership
License Assigment (in use like a seat)
Shipping Address
Shipping Country
Taxes
Billing Address

How should I structure the openapi and database model?
In the case of taxes, for example, I need to know all of these

    "Province_State_Tax_Id": "1",
    "Province_State_Name": "Alberta",
    "Province_State_Abbreviation": "AB",
    "Province_State_Tax": "5.00",
    "Country_Id": 38

But I also need to query extra things such as who is the owner of the account, but at the same time maintaining the organization separated from owner (that person could leave the company if it’s an employee). I have functions such as license transfer from one account to another, dealers.

Should I make all those parts separate, then compose them together? When to know when it’s too much separation and too little?