No preference or requirements to answer for a particular database platform.

learn more… | top users | synonyms (1)

24
votes
13answers
9k views

How to implement a 'default' flag that can only be set on a single row

For example, with a table similar to this: create table foo(bar int identity, chk char(1) check (chk in('Y', 'N'))); It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
14
votes
5answers
500 views

Indexing from start or when performance problem arises?

My question is regarding use of indexes. Should I start indexing right from the start or when performance problem arises? We can also create temporary index while executing a query. What are the ...
13
votes
4answers
2k views

Why won't some DBMS's allow rollback for certain DDL statements?

Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow ...
13
votes
2answers
3k views

Open Source Business Intelligence/DWH solutions [closed]

I wonder that this question hasn't already been asked. Google only has very few results for me that don't show a high quality tool What are some Open Source (also free is ok) solutions for Data ...
9
votes
2answers
421 views

Which areas of a DBA's knowledge should a developer delve into? [closed]

I must admit the question is quite broad, so I'll try narrowing it down a bit. In our company we are 3-4 developers and have some SQL Server based installations running at our customer's sites ...
8
votes
4answers
555 views

Arbitrarily ordering records in a table

A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
6
votes
1answer
875 views

Password hashes: Fixed-length binary fields or single string field?

I'm currently having am amusing debate with a friend, and we simply can't agree on the best method to store salted passwords in a database. The two options on the table are: Storing the hash and the ...
5
votes
2answers
223 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
2
votes
1answer
482 views

Pros and Cons of having additional columns in join table of many to many relationship

I searched but did not find any discussions on this topic. Are there any pros and cons on creating many extra columns on joined table? Does it affect speed of data retrieval because we are joining 3 ...
2
votes
2answers
106 views

Associating on the ID of an association table

Sorry about the title, I couldn't find anything better. Suggestions are welcome. Say we have two tables, Suppliers and Products. The same product can come from many different suppliers, so we create ...
2
votes
1answer
597 views

Database design with different user roles connected by different relationships to one organizaiton table

An user is connected to an organization by some role. Lets say there are 2 roles freelancer and employee. One employee can only be a part of one organization and if he is an employee then he can't be ...
2
votes
2answers
312 views

How to establish a Two-to-One table relationship (PK-FK-CK)?

My question is about DB design and its principles. I am working on a Python application where objects are mapped to database tables. I have Projects and People objects, mapped to their respective ...
1
vote
1answer
298 views

One address table for many types of addresses

I have an application where I have to store adresses of multiple types of location. For example, my app requires addresses of people, hotels and event venues. Should I create separate tables for ...
1
vote
1answer
104 views

Is it possible to query “NOT IN” in NO SQL?

I asked the same thing about a specific NoSql engine (Big Table) on SO. It seems that Big Table doesn't support NOT IN queries? Is that possible in any other NoSQL databases? Will it ever be possible ...
1
vote
2answers
315 views

Extensible Asset Database Schema

Background I work for a decent sized company in the hardware sector. Currently, we have a number of varying assets that need to be tracked. Until recently, this was done by hand as was an ...
1
vote
2answers
176 views

Usage of foreign key when the referenced table data will not change

I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely. For e.g., the user is presented ...
1
vote
1answer
32 views

database DDLs and implicit commit

I've been taught that in RDBMS any DDL always causes an implicit commit, ending any active transaction at the time the DDL is issued. Now I found this page on the PostgreSQL wiki that states that ...
0
votes
2answers
152 views

How do you store tabular data in an RDBMS?

First: I really haven't got any idea how to google for this. If you got one, leave a comment. If I want to store arbitrarily large tables in a database, how should I set up my database tables? ...
0
votes
0answers
17 views

Storage strategy for polynomials

What is the most space efficient way to store arbitrary length univariate polynomials? I have a large amount of polynomials that look like 3*x^7 + 12*x - 13 9*x^32 - x^14 + 2*x^3 - 9 ... Each ...
0
votes
0answers
15 views

Can a slow SELECT statement in READ COMMITTED isolation level read content from concurrent committed UPDATE?

Let's say we have a big table with hundreds of million of rows (or more). On one connection, we perform a query requiring full table scan under READ COMMITTED isolation level: SELECT * FROM ...
0
votes
1answer
38 views

EAV table question

What's the best design for taxonomies in EAV tables? taxonomy id autoincrement name unique terms id autoincrement tax_id references taxonomy(id) name text unique value text ...
0
votes
0answers
26 views

How to distribute/release/publish a database schema

I want to distribute/release/publish a database schema to multiple 3rd parties (e.g. I'll produce a zip for them to download and they'll set up their database using it). They aren't all using the same ...