Tagged Questions
1
vote
1answer
31 views
Restrict two specific column values from existing at the same time
I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed.
I would appreciate any help creating a constraint that will enforce this.
CREATE TABLE example
(
...
3
votes
1answer
76 views
For alignment optimized table is bigger than original table - why?
In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
-1
votes
1answer
41 views
Database Design for classifieds site [closed]
I'm creating a classifieds site where there are many categories and subcategories.
1) Each category has different fields.
2) Each listing has one or more images
I need help with the database design. ...
0
votes
2answers
40 views
Auto increment char varying column prefixed with some characters
I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well.
Is there a way to achieve ...
0
votes
2answers
60 views
Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases
We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL.
In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
2answers
59 views
How to change schema so that account_id reference is unique among 3 tables
Original image:
Updated to have more-correct terminology and an 'is_debit' column:
I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting.
'jackpots', ...
0
votes
1answer
28 views
Mixed search in multiple columns
I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance ...
1
vote
1answer
47 views
Update new column with value from old columns for all the rows
I've got an odd db design in place that was here when I got on my new work space. We have a column named params which contains some string data which appears that it belongs in more than one column, ...
1
vote
0answers
15 views
Optimize finding the next batch of data from A to be crunched and inserted into B
I'm using PostgreSQL to store data produced by a large distributed computation. There are several stages to this computation; the idiom we've settled on is that there's a table containing the results ...
0
votes
1answer
48 views
Database design Problem for the web-app
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 ...
1
vote
2answers
74 views
Postgresql: datatype for email address
What would be the right datatype to store email addresses in PostgreSQL?
I can use varchar (or even text), but I wonder if there is a more specific data type for emails.
1
vote
1answer
52 views
Import using pgAdmin3 produces ERROR: duplicate key value violates unique constraint
I am importing from the GUI a CSV file into a table. I have created the following table:
CREATE TABLE jira_tickets.daqa_rpt_tbl
(
daqa_report_id bigint NOT NULL DEFAULT ...
1
vote
0answers
79 views
Postgresql - How to Convert all Column Names to Lowercase
I have a travel survey with a series of tables (lots and lots of columns) that I exported from MS Access into a postgres database. I've search around a lot for an explanation of how to convert all the ...
1
vote
2answers
139 views
When normalizing a relational database, should multiple “type” tables be combined into one?
Before making substantial changes to the content, functionality and design of my website (I'll be refactoring everything, almost a complete rewrite), I'm setting up a new PostgreSQL database to hold ...
5
votes
1answer
146 views
HStore vs. multiple tables with inheritance - do I have a good use case for HStore?
My broader question here has to do with when it is appropriate to use HStore vs. multiple tables vs. one table for storing document-like objects.
Now, for my localized example: I’m designing a ...
1
vote
3answers
98 views
Mass data upload from Excel - technology
I am a Developer seeking help in terms of DB technology for a specific requirement. I have done some research but can not conclude with list.
Requirement:
There will be a web application which will ...
1
vote
1answer
112 views
What's better here - a single column or multicolumn primary key?
Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
0
votes
1answer
162 views
postgresql bigserial or sequence using jpa
I'm a java developer working with jpa, before to define the indexes in my entities i would like to known which one is faster o better to use?.
0
votes
0answers
31 views
Organizing a couple tables in postgresql
I have a data structure that is represented like this:
{
'question_id': 1,
'is_required': False,
'question_text': [
{
'id': 1,
...
0
votes
0answers
52 views
Reduce read write delays in specific database setup
I have a database with a large table with 15 columns (type double). It contains a large amount of data (~ 100 rows of data for every second. Holds 3 days of data). I also have a real-time program that ...
1
vote
6answers
461 views
Efficiently storing irregular/repeating intervals (think calendar/events)
I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing.
Right now ...
1
vote
1answer
102 views
Spliting Datetime into 2 Columns for Restaurant Reservation Schema?
Using PostgreSQL.
The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...
3
votes
1answer
1k views
Fixing table structure to avoid `Error: duplicate key value violates unique constraint`
I have a table which is created this way:
--
-- Table: #__content
--
CREATE TABLE "jos_content" (
"id" serial NOT NULL,
"asset_id" bigint DEFAULT 0 NOT NULL,
...
"xreference" varchar(50) ...
0
votes
1answer
524 views
Best Approach to storing chat history?
I'm building a chat application (Yes, another one :P) and am really curious about the best way to store a chat's history(String username, String Message, Time, and possibly string channel depending on ...
1
vote
1answer
73 views
Best approach on storing/displaying sanitized data
Surfing the web I find two approaches on storing data in databases.
Approach 1:
Store data in the database with the html-specialcharacters sanitized
$userdata = $_POST['field'];
...
0
votes
1answer
111 views
Choosing between single table and multiple table database scheme [closed]
I'm building an application which saves addresses in a PostgreSLQ 9.3/PostGIS 2.1.1 database. Currently I am trying to find an efficient database schema, so far I've come up with two that would ...
1
vote
2answers
139 views
To normalize or not to normalize for few distinct values
Assuming in a Postgres database, you have a table called party, which can have less than 5 well-defined party_types such as 'Person' or 'Organization'.
Would you store the party_type in the party ...
3
votes
1answer
68 views
Non-overlapping rectangles constrained to a boundary
I am trying to model placement of parts on a circuit board. Without any
meaningful constraints, my basic schema looks like this:
create table part (
part_id bigserial primary key,
name text ...
2
votes
2answers
112 views
Most performant way to fetch last record
I have a Postgres table Prices with the columns
price (Decimal)
product_id (Int)
Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table ...
3
votes
1answer
111 views
Enforcing constraints “two tables away”
I ran into some trouble modeling an electrical schematic in SQL.
The structure I'd like to capture is
part ←────────── pin
↑ ↑
part_inst ←───── pin_inst
where "inst" is short ...
5
votes
1answer
117 views
Hierarchical structure with limited number of records per user
I am planning schema for an affiliate network. For all hierarchical queries I am using Postgres tablefunc extension but here is another problem.
Any user can refer only 3 other users at max.
So for ...
5
votes
0answers
156 views
Storing and querying rolling data in PostgreSQL
I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
3
votes
1answer
76 views
Geographic Synonyms
Let's say I have a table representing principal country divisions (ex States):
create table principal_country_divisions (
id int primary key,
name text not null,
country_code char(2)
);
insert ...
2
votes
1answer
250 views
Two-column foreign key constraint only when third column is NOT NULL
Given the following tables:
CREATE TABLE verified_name (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
UNIQUE (name, email)
);
CREATE ...
2
votes
1answer
478 views
Need for indexes on foreign keys
I'm struggling with indexes, primary keys and foreign keys... And the need of having them all.
If I have two tables, both of them have an integer as a primary key.
The first table references ...
0
votes
2answers
122 views
Database design for holding read/unread content state
In forum application I have member table and post table. What I need is to keep track of what posts have been read by which members. What I thought about is to have a junction table with two columns: ...
0
votes
1answer
66 views
Nullable One To Many w/Join Table - Indexing
I have a question regarding database design, I am working with PostgreSQL using Hibernate for ORM, the design I inherited includes the following tables
users
user_id (pk)
user_name
user_dob ...
...
0
votes
1answer
173 views
How can you have a combined primary key for one table?
I have exactly the same question as posted here.
But in my case I do not use MySQL but Postgres. Can this be done with Postgres? All things indicate I can not use a combined index in Postgres, so ...
2
votes
1answer
132 views
PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'.
Various revisions of this SQL isn't constraining '-/+infinity' input:
CREATE TABLE bill
(
id ...
1
vote
1answer
228 views
Design DB for users with different information fields?
Let's say I want to register all faculty of a university and they are in different fields and have different resume information. Here we have some common fields and some field specific fields. for ...
2
votes
3answers
299 views
Many to many link tables with history? [closed]
While creating a PostgreSQL database of customer subscribed services with DNS domains, I want to:
enable customers to subscribe to multiple services
each service can have multiple domain names per ...
0
votes
1answer
134 views
Entity with >150 attributes: One table or many tables with relationships
I have entity with 170 attributes. It's data from vehicle monitoring. Every entity has datatime label and unique id of gps terminal. Datetime and id of terminal - these are conditions for GROUP BY ...
0
votes
2answers
254 views
Which schema is better for a shopping project? [closed]
I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
1
vote
1answer
1k views
Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]
I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
0
votes
1answer
55 views
Creating groups and members [closed]
Is this a good idea to manage members of a group like so:
*Assuming members can belong to one group and no other
*Simplified model
Group:
name
someid
User:
name
ForeignKey(Group)
state ...
-3
votes
1answer
77 views
Make “NOT NULL” default in Postgresql 9.2 [closed]
I use NOT NULL a lot when creating columns.
Is there a way to make this default when defining columns? In other words, make column is_nullable default to NO. (I realize this would make it harder ...
4
votes
2answers
2k views
How to choose a collation for international database?
I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the queries results is ordering it according to the user's language during the ...
2
votes
1answer
393 views
Best practice for storing record metadata
What is the best practice for storing metadata of individual records in a database?
I need to store common meta data such as creation time and time of last update for many tables in my database. I ...
0
votes
2answers
150 views
Mirrored tables on PostgreSQL
I have some values under "account" and I need to have the same values under "invoice", so, I'm trying to find a way to symlink my tables, so every time one gets updated, the another have the new value ...
2
votes
4answers
1k views
Database design - People and Organisations
Databases are not my primary skill and I am finding I need some assistance please.
The software we are building has "Customers".
The Customer could either be a Person or an Organisation.
I really ...
