Tagged Questions
1
vote
3answers
52 views
SQL : Create a full record from 2 tables
I've got a DB structure as is (simplified to maximum for understanding concern):
Table "entry" ("id" integer primary key)
Table "fields" ("name" varchar primary key, and others)
Table "entry_fields" ...
0
votes
1answer
24 views
how to cause a trigger to calculate balances in an accounting software using postgres
Following to my earlier question that was answered, I would like to know how to do the similar balances calculations using postgres triggers.
how to calculate balances in an accounting software using ...
1
vote
2answers
38 views
Foreign key of serial type - ensure always populated manually
I have two tables: countries and regions.
CREATE TABLE Countries(
id SERIAL,
name VARCHAR(40) NOT NULL,
PRIMARY KEY(id)
)
CREATE TABLE Regions(
id SERIAL,
...
1
vote
1answer
22 views
Determine whether constraint name is system generated in Postgres
I am trying to find if a constraint name is 'system generated' or 'manually given at the time of creation'.
MS SQL Server has a column 'is_system_named' in sys.key_constraints:
1 = Name was ...
2
votes
1answer
43 views
Serial numbers per group of rows for compound key
I am trying to maintain an address history table:
CREATE TABLE address_history (
person_id int,
sequence int,
timestamp datetime default current_timestamp,
address text,
...
1
vote
1answer
18 views
Schema design: Entity with common fields across subtypes, but where subtypes only allow subset of possible values in common field
Overview: I am trying to represent several types of entities in a database, which have a number of basic fields in common, and then each has some additional fields that are not shared with the other ...
0
votes
1answer
32 views
Finding out the total count of each tag in a data set
I have a set of Item-s and each of them has a set of Tag-s. I want to have a DB SELECT which, for some (large) subset of these items, returns the total count of each Tag in that Item sub-set.
Is ...
0
votes
2answers
54 views
What is the best practice to expand GCE root persistent disk size of the running instance
I'm running an instance for PostgreSQL whose disk size is 10G and it is almost full now.
Creating a new instance for PostgreSQL with the larger disk could be an option, but it will take so much time ...
google-compute-engine
4
votes
3answers
71 views
SQL: How to deal with mutually recursive inserts
I have a model that defines mutually recursive tables:
Answer
questionId QuestionId
text
Question
text
correct AnswerId
What do I need to do to actually insert a question? I need to know ...
1
vote
1answer
49 views
Database Schema Design: Tracking User Balance with concurrency
In an app that I am developing, we have users who will make deposits into the app and that becomes their balance.
They can use the balance to perform certain actions and also withdraw the balance. ...
3
votes
1answer
49 views
Uniqueness validation in database when validation has a condition on another table
I asked a similar question here at Uniqueness validation in database when validation has a condition but my requirements have changed, hence this question.
Using uniqueness validations in Rails is ...
-1
votes
1answer
35 views
DB design Employee working schedule
I am designing a employee DB. I wanted to store their availability time for next 3 weeks. Here are my requirement.
Employee standard working time is defined in table or field (mostly between 9 to 5)
...
0
votes
2answers
59 views
Which is better, have a primary key composed of an integer and a foreign key or have a primary key autoincrement and a foreign key?
I have a problem, the database admin have the follow structure:
As you can see the primary key of the table TCModulo is a composed key of the ID_modulo and ID_sistema which is a foreign key of the ...
0
votes
2answers
44 views
Database Design For Self Reference
I need to design a Right table in such a way that it can refer to itself. I already have a roles and users table.
User can have multiple roles and a role can have multiple rights.
But the current ...
2
votes
1answer
34 views
When to add records to a user_posts table to track 'likes'
I have a Groups model that has_many Topics.
The Topics model has_many Posts, my Users model only read posts etc.
I also have a users_posts model which is used to keep track whether a user has read the ...
0
votes
1answer
43 views
one-to-one or one-to-many: one table with hundred columns or moving columns to an extra table
i am trying to find the best DB design for the following problem.
i have 20000 data sets which look like this:
1. id, name, color, width, xxxx ... 150 attributes
2. id, name, color, width, xxxx ... ...
0
votes
1answer
37 views
How to support variable number of one-to-many relationships in database
I am trying to design a Person database. The requirement is that a Person can have one or more varying number of children, cars, jobs, and homes.
So, currently, the way I have designed this is:
...
0
votes
2answers
29 views
Get sum of integers for UNIQUE ids
In a game using PostgreSQL 9.3 as backend I am trying to limit the number of games played by a user per week.
I have prepared an SQL Fiddle, but unfortunately it doesn't work.
My (test, not ...
-1
votes
1answer
48 views
Database design for the web-app [closed]
Hello I am in a problem of designing a web-app database schema .
Suppose i have 10 shops and every shop gives deals on every different day Sunday Monday and so on and for limited time period like ...
0
votes
1answer
36 views
Groups of independent tables, how to deal with?
So I have a situation where I have Postgres database with a large number of groups of 2-3 tables that don't have anything to do with anything else in the database (but do reference each other within ...
0
votes
1answer
45 views
Associating two tables that both belong to a third, independent table
I have three tables:
Topics Jobs Requests
----- ---- --------
title description request
description worker_id user_id
topic_id ...
1
vote
1answer
54 views
How to build relational tables in a two-sided marketplace
I'm really struggling with this problem, would love some additional thoughts. Here's the basic context:
Users can both list items to be lent out, and make requests for items to borrow
Requests are ...
0
votes
2answers
43 views
How to create a database that shows a calendar of availability for a set of apartments?
Context:
The best example is AirBnB. Let's say I have 5 apartments. Each apartment has a calendar that represents it's availability. When a vacationer travels to my city and searches for apartments ...
1
vote
1answer
56 views
create unique constraints per user in tables with n:m relation
I have asked a question a few moments ago, create unique constraints per user and the answer was very simple.
For creating a unique index on a column, but on a per user basis, all I have to do is:
...
0
votes
1answer
40 views
create unique constraints per user
I am building a small app and at this point I am creating the database schema. I use PostgreSQL with Sequel and I have the two migrations:
Sequel.migration do
change do
...
1
vote
2answers
33 views
Add serial value across multiple tables
I have the following two tables in my Postgres database:
CREATE TABLE User (
Id serial UNIQUE NOT NULL,
Login varchar(80) UNIQUE NOT NULL,
PRIMARY KEY (Id,Login)
);
CREATE TABLE ...
1
vote
1answer
55 views
Database Design to handle newsfeed for different activities
I am going to create a new project, where I need users to view their friends activities and actions just like Facebook and LinkedIn.
Each user is allowed to do 5 different types of activities, each ...
3
votes
1answer
33 views
Query across timezones
I'm developing an app where a user can request that an email be sent to them at a specific time every day in their timezone. For example User A lives in London and schedules an email at 2pm every day ...
2
votes
2answers
66 views
Database design - should two projects share the same table?
Background:
Two projects (A & B) under design at the same time both needs a new table(called DocumentStore) to store document/file under postgres.
But business logic around the document storage ...
2
votes
3answers
75 views
PostgreSQL - How to JOIN M:M table the right way?
My database structure looks like this:
CREATE TABLE categories (
name VARCHAR(30) PRIMARY KEY
);
CREATE TABLE additives (
name VARCHAR(30) PRIMARY KEY
);
CREATE TABLE beverages (
name ...
0
votes
1answer
35 views
Primary key for multiple column in PostgreSQL?
How to provide primary key for multiple column in a single table using PostgreSQL?
Example:
Create table "Test"
(
"SlNo" int not null primary key,
"EmpID" int not null, /* Want to become ...
3
votes
2answers
146 views
Propagate primary key to child tables
I want to propagate the value of a primary key column from a parent table to a specific child table when inserting a new row.
For explanatory purposes I've created following tables:
Create TABLE ...
4
votes
1answer
83 views
Enforcing supertype & subtype data integrity without stored procedures, triggers, or UDFs
I run a small food production business and I need to manage customer orders. I have built a conceptual data model of this aspect of my business but I need some pointers on how to fully implement this ...
1
vote
3answers
78 views
Create Alias for PostgreSQL Table
I have a table called assignments. I would like to be able to read/write to all the columns in this table using either assignments.column or homework.column, how can I do this?
I know this is not ...
0
votes
1answer
123 views
Inner join performance
I have a table that has a lot of foreign keys that I will need to inner join so I can search. There might be upwards of 10 of them, meaning I'd have to do 10 inner joins. Each of the tables being ...
0
votes
1answer
106 views
what is a better way than arrays overlap? (postgresql)
I am building an app for a business company, and they need to control who sees which reports by project and roles, the report can belong to one project and can be seen by many roles (employees roles).
...
1
vote
0answers
30 views
Is this a good idea to store relations to many different tables in one field? [duplicate]
I have 4 basic models which are stored in following tables:
users
images
videos
posts
In application logic user may "love" image or url or post (for example user clicks little heart next to image ...
0
votes
1answer
42 views
How to keep track of the origin of data in a large data set
We are doing a one time migration of data (about 100 million rows from different tables with the biggest table having about 40 million records). After the one off migration, we will have about 5 ...
0
votes
1answer
60 views
How to decrease query execution time with DISTINCT?
I have slow query:
SELECT DISTINCT ON ( topic_category_id ) * FROM topic t
WHERE abstime ( post_time + 24 * 3600 ) >= now ( )
ORDER BY topic_category_id, post_time DESC LIMIT 10;
It's ...
0
votes
1answer
57 views
uploading sql ddl file to postgresql database
I am relatively new to working on Databases. I created this SQL file and I want to upload this DDL (Data Definition Language) File to my PostgreSQL server. My server currently runs on Ubuntu 12.04. ...
-1
votes
1answer
32 views
How to auto increment id with a character
How do I auto-increment an ID of a member in my table together with a character with it
for example: M_01, M_02, M_03:
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT ...
0
votes
1answer
36 views
What is the best practice to select users from DB ordered by online?
I have table 'user', which has about 1 million users. I have query which select online users like this
select *, CASE WHEN abstime ( last_login_time + 600 ) >= now ( )
THEN 3 ELSE 1 END ...
1
vote
0answers
71 views
Record id field value on a shared-schema multi-tenant relational table [closed]
When designing a multi-tenant database for use in a SaaS system, in an architecture where all tenants share the same tables, what is the best-practice for the type and value of the field that is ...
-1
votes
1answer
36 views
What is the best scenario in this case?
I have two tables: Employee and Training, Employee table has two columns: ID (numeric) and Name (text).
Training table also has two columns: No (numeric) and TrainingName (text).
My boss asked me ...
1
vote
1answer
39 views
SQL save different versions of same product in table
I have a products table in postgres that stores all the data I need. What I need to work out the best way to do:
Each product has a different status on the way to completion - machined, painted, ...
3
votes
2answers
88 views
Prevent insert if condition is met
I have a table Content like this:
id | text | date | idUser → User | contentType
And another table Answer:
idAnswer → Content | idQuestion → Content | isAccepted
I want to ensure that the ...
0
votes
1answer
50 views
Look up in a white space separated string in Postgres
I have a character varying field in postgres containing a 1-white-space-separated set of strings. E.g.:
--> one two three <--
--> apples bananas pears <--
I put --> and <-- to ...
0
votes
0answers
40 views
UML ERD diff generate database migration
Do you know some tool which allow generate database migrations for change(diff) in UML ERD diagram? I don't find that functionality in visual paradigm, maybe is it available in enterprise architect, ...
0
votes
1answer
43 views
Am I using find_or_create_by method properly?
I am trying to populate a new table from an existing database but my method does not seem to be working properly. Below is my code.
class CreateEmployees < ActiveRecord::Migration
def change
...
1
vote
2answers
65 views
How to check role of current PostgreSQL user from Qt application?
I have some application based on Qt library and using QPSQL driver.
In PostreSQL defined a few user roles (e.g: admin, operator, user). My application creates a connection to postgres server under ...
