Tagged Questions
1
vote
1answer
28 views
Prevent overlapping times in start-time, end-time columns
We have a use case where we are storing start_week and end_week in a row and want to make sure that these values across rows are never overlapping i.e. they are exclusive. Examples below
Ideal
code - ...
0
votes
2answers
30 views
Database setup and design for 15 questions game - PostgreSQL and Rails
I am working on a small game where people have the ability to create 15 question quizzes. I am struggling to figure out how to structure my database for the best optimization. The questions each have ...
0
votes
1answer
17 views
Specify that another field should have the same value as the id field on INSERT
In a Postgres table I have an id field (system generated identifier) and an other_id field. During insert, if the other_id field is not supplied I'd like the other_id to take on the value of the id ...
0
votes
3answers
53 views
Is it possible to name SQL result columns from rows in another table? (Postgres)
Basically I have users with a dynamic attributes table. Simplified:
SELECT * FROM users;
id | email
-----+------------------------------------------
1 | ...
1
vote
2answers
27 views
Database optimization - Encoding fields
A bit of a theoretical questions.
Was just wondering is there a way to optimize data fields?
Say for a given field you only have 3 possible strings but those are for some reason very long (say 50 ...
1
vote
2answers
44 views
Foreign keys referring other foreign keys in PostgreSQL
In PostgreSQL I have a database, which I intend to make the following table declaration:
CREATE TABLE canvas_user (
id INTEGER,
login_id VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(355) ...
1
vote
0answers
32 views
A table with thousands of records, and each record hundreds of attributes with PostgreSQL?
I am currently running PostgreSQL and I have a requirement of creating a table called Template.
The users will be able to create lots of templates, and each template will have hundreds of attributes ...
0
votes
1answer
32 views
Database design for optional fields
I've got 3 tables:
orders
line_items
products
They're setup as:
order has many products through line_items
This allows me to store in line_items such things as the product_id, quantity, ...
0
votes
1answer
51 views
Model Recursive relationship without graph database
We use Postgres as our database. Now have a new requirement to model relationships like follow. Though it seems like a good fit for a graph database, there is no room for using graph db as postgres ...
1
vote
3answers
62 views
How to allow only one row for a table?
I have one table in which I would like only one entry. So if someone is trying to insert another row it shouldn't be allowed, only after someone deleted the previously existing row.
How do I set a ...
0
votes
3answers
61 views
Adding columns versus adding rows - which offers better performance?
Searched and searched. Not sure how to use Explain/Analyze to answer this, without constructing really large test tables and I don't have the means or time to pursue that. Certainly someone can ...
1
vote
3answers
69 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
33 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
41 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
24 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
50 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
19 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
33 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
64 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
79 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
2answers
65 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
53 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
59 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
60 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
35 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
47 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
49 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
46 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
62 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
56 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
72 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
51 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
35 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
61 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
36 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
68 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
80 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
41 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
155 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
85 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
98 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
184 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
123 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
63 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 ...
