For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.

learn more… | top users | synonyms

6
votes
12answers
4k views

Over normalization in database

Currently I am working on creating a new database schema as part of a major new product. Our previous customer address records (in a previous product) look something like this in our current schema ...
8
votes
1answer
4k views

Database Application Design Best Practices [duplicate]

Once upon a time it was common practice for both the business logic and database logic to be written in the same language (eg PL/SQL, Transact SQL, etc), more recently the practice is to separate the ...
4
votes
1answer
1k views

Are rollback and roll-forward transactions equivalent for fault tolerance?

I've been reading about systems that employ transactions to provide fault-tolerance -- SQLite, for instance, implements a journal that allows the database to be rolled back to a known state in case of ...
1
vote
2answers
1k views

Price Comparison Database Scema

I have a bunch of data from multiple retailers. Each product that each retailer sells may or may not be sold by another retailer in the database. If multiple retailers sell the same product, then the ...
3
votes
2answers
345 views

Which database paradigm is suggested for a site with search capabilities?

Currently I'm trying to select the best DB paradigm to implement a web site with full-text search and faceted search capabilities. I have an important functional requirement: The database paradigm ...
4
votes
2answers
918 views

How to design a website with search capabilities?

Suppose you want to create a web page with some search capabilities over a database. Search has to be restricted both on some categorization of data and open text given by the users. Functional and ...
5
votes
7answers
2k views

Priority list of tasks stored in a database

I am trying to think of the best way to do the following: I have a list of tasks stored in the database. A task has a priority assigned to it. You can change the priority of a task to reorder the ...
15
votes
2answers
5k views

What is the difference between collation and character set?

I have a general question about databases. We usually use the term collation with databases. I would like to know how its different from character set. I guess collation is a subset of character set. ...
2
votes
2answers
1k views

Database for storing books by grouping, their pages, and the table of contents

I'm looking for a better way to deal with the design I have here, I'm not confident it's really suitable. To give this some context, lets say we have a bunch of groups, one group could be "SQL", and ...
2
votes
3answers
292 views

Fault tolerance through replication of SQL databases

Suppose the middle tier servers are replicated 3 way and the backend database (MySQL, PostgreSQL, etc...) is replicated 3 way. A user request ends up creating some user data in the middle tier servers ...
15
votes
8answers
2k views

Handling deleted users - separate or same table?

The scenario is that I've got an expanding set of users, and as time goes by, users will cancel their accounts which we currently mark as 'deleted' (with a flag) in the same table. If users with the ...
23
votes
4answers
11k views

EAV - is it really bad in all scenarios?

I'm thinking to use EAV for some of the stuff in one of the projects, but all questions about it in stackoverflow end up to answers calling EAV an anti pattern. But I'm wondering, if is it that wrong ...
4
votes
1answer
243 views

What is a buffered write scheme?

I received this response from Jeff while researching hit counters. He said page hits are incremented in a buffered write scheme, and that a views table does not exist. Can you explain what a buffered ...
9
votes
2answers
351 views

When is cloud database right for me?

Some say that DBaaS (database as a service) -- aka cloud database -- is not suitable for Business Intelligence (BI), analytics (OLAP) or archiving. Is this true? More generally, when DBaaS is the ...
6
votes
5answers
395 views

Anti Identity Column

I recently came across some code which generates an identity value for a key: DECLARE @ID INT; SELECT @ID = MAX(ID) + 1 FROM Person; INSERT INTO [Person] ([ID], [FirstName], [LastName]) VALUES ...
11
votes
10answers
946 views

How could RDBMSes be considered a fad?

Completing my Computing A-level in 2003 and getting a degree in Computing in 2007, and learning my trade in a company with a lot of SQL usage, I was brought up on the idea of Relational Databases ...
3
votes
3answers
778 views

Quickly Large Data Pivoting

We are developing a product which can be used for developing predictive models and the slicing and dicing of the data in order to provide BI. We are having two kind of data access requirements. For ...
2
votes
8answers
442 views

How Would You Design This Table?

I have to create a table where each row needs to store 50 number values. Each row will always need to store 50 number values. If this was a smaller number of values, I would just make fields for ...
1
vote
9answers
981 views

Sr. Dev made a database I disagree with. Advice sought [closed]

I need to run to work soon, so this will be brief. I've only been with the company for a couple weeks. It is a good company, this is a contractor who just happens to be twice my age. I am new to ...
14
votes
7answers
3k views

What special considerations are needed when designing databases to hold financial records?

I hope this question isn't too broad. In the future I may need to add some accounting and financial-tracking systems to some applications (mostly web-based applications, but my questions pertains to ...
4
votes
5answers
1k views

graph or relational database?

I'm starting to think that a lot of my tables could be replaced by only a graph db: For example: I have 4 tables: accounts, votes, posts, relationships but I can represent all these in a graph table ...
47
votes
13answers
5k views

Is it necessary to create a database with as few tables as possible

Should we create a database structure with a minimum number of tables? Should it be designed in a way that everything stays in one place or is it okay to have more tables? Will it in anyway affect ...
2
votes
1answer
2k views

How does Facebook calculate mutual friends?

How does Facebook calculate mutual friends? Does it cache all mutual friends for each user? Does it use MySQL to calculate mutual friends with a query?
6
votes
9answers
11k views

Is there a large bank using Mysql or PostgreSQL? [closed]

I always thought the largest scale of banks use Oracle. However, there is no proof they really use Oracle instead of Mysql or PostgreSQL, nobody knows the secret. Any idea what they really use? Can ...
4
votes
6answers
132 views

Arguments for storing binary asociations in a single field vs. in a mapping table?

When do you draw the line between relational tables, and fields holding multiple values of a particular type. Let me illustrate in an example below where a PokeMaster can be associated to multiple ...
6
votes
3answers
523 views

Types of databases

I read that there exists three main types of databases * Transactional (Client-Server database// OLTP database) * Decision support system (DSS) (Data warehouse database // Data mart // Reporting ...
1
vote
3answers
129 views

Schema/Code for respecting different schedules

We accept leads from lead providers. We only allow certain providers to post leads to us during certain days/times. Currently, we have 3 schedules, but we might add 1 or n more in the future: Sunday ...
12
votes
7answers
12k views

Best practices to follow with database indexes [closed]

What are some DOs and DONTs for improving database performance using index? A DO would be a case in which an index should be created, or another indexes related tip that will improve performance. A ...
3
votes
2answers
140 views

Best Way to Express Meta-Info on Missing Data?

I work in healthcare IT, reviewing data management processes of various observational studies. One problem I have repeatedly faced is the poorly encoded data, especially when some values are missing. ...
18
votes
9answers
5k views

Best way to reference static database data in code?

Many applications include 'static data': data that doesn't really change during the lifetime of the application. For example, you might have a list of Sales Areas that is likely to be a fixed list for ...
17
votes
9answers
2k views

Constraints in a relational databases - Why not remove them completely?

Is there any reason to build constraints between tables (inside SQLserver) nowadays? If so, when? Most applications in my area are built on object principles and tables are joined on demand. Demand ...
23
votes
13answers
2k views

How To Deal With Terrible Design Decisions [duplicate]

I'm a consultant at one company. There is another consultant who is a year older than me and has been here 3 months longer than I have, and a full time developer. The full-time developer is great. ...
6
votes
5answers
2k views

database schema with unlimited hierarchical data that is modified a lot

I am trying to set up some sort of collaboration app/forum with unlimited threading; no difference between posts, threads, or forums. Any post can be answered an unlimited amount of time, and the same ...
10
votes
4answers
4k views

Weaknesses with different types of NoSQL databases

Here's my question: What are the weaknesses with different types of NoSQL databases? Specifically, what're the weaknesses of key-value stores, graph data stores and document stores? I've had an easy ...
24
votes
6answers
1k views

Does the advent of the SSD have any implication for database optimization?

Today I was browsing through a book on SQL Server optimization and it seemed that a certain amount of the ideas were based on a linear model of storage. As SSDs have a completely different storage ...
10
votes
6answers
37k views

what do database programmers do?

Every time I read about Oracle programmers etc, I get confused. I don't know what exactly they do. From my understanding, application programmers need to develop the core functionality. The libraries ...
2
votes
3answers
895 views

How do I properly design a many-to-many (charges/payments) accounting system?

This is just as much a db/programming question as an accounting/math question, so I'm actually posting to all relevant boards. I appreciate your patience in advance. Basically I'm having trouble ...
5
votes
3answers
336 views

What design would you propose to log reporting data so that the capture of, say, an additional form field can implemented easier?

Why is there an emphasis to log reporting information through an application to a separate database? I have seen designs where a logging manager is available to every business layer class whether it ...
3
votes
3answers
1k views

Naming convention for associative entity

When designing Many-to-Many relationship on a database, we need to put another table in the middle so it become 3 tables with 1-to-many relationship. Wikipedia told me that it's called associative ...
5
votes
1answer
2k views

What would your DB Schema look like if you are building a Reddit/Digg-like site? [closed]

Just found out something rather interesting about Reddit's database schema: The way we’ve changed is we use an “open schema”. Sometimes it’s called “entity attribute value”. It’s basically ...
10
votes
3answers
2k views

Storing n-gram data

I was hoping to brainstorm a little bit on the subject of storing n-gram data. In my project, I am trying to solve linguistic problems where I know all (n-1) data items and want to statistically guess ...
0
votes
1answer
391 views

Transactional database and Datawarehouse on the same Postgresql cluster?

Is it conceptually feasible to have on a Postgresql Cluster a transactional database and at the same time a datawarehouse that would get feeded by the transactional database ?
3
votes
5answers
625 views

Problems in coordination between software and database teams

This is pretty much a "software process" question: When your organization is split into two teams: software team and database engineering team (the organization is in ETL/BI/Data mining delivering ...
1
vote
4answers
412 views

Schema Design : Advice Needed

I have a distributed data collection system where I have first layer as regional Databases where all collected data is initially stored and master database where all the data is consolidated, what are ...
7
votes
6answers
1k views

Is the “One Description Table to rule them all” approch good?

Long ago, I worked (as a client) with a software which use a centralized table for it's codified element. Here, as far as I remember, how the table look like : Table_Name (PK) Field_Name (PK) Code ...
0
votes
3answers
2k views

Any examples of using Visio to document software? [closed]

I have Visio 2010 and want to learn how to use it to document software. Does anyone have examples of what Visio does well? Are there any things that Visio can do, but there are better tools for the ...
1
vote
1answer
750 views

DB Schema for ACL involving 3 subdomains

I am trying to design a database schema for a web app which has 3 subdomains: a) internal employees b) clients c) contractors. The users will be able to communicate with each other to some degree, ...
25
votes
8answers
8k views

Front end first or Back end first. Of the two which is a Good system design pratice?

I have a client right now requiring me to develop a school enrollment system. Now this is the first time im having this kind of challenge. Most of the past software that i created are not that ...
15
votes
6answers
771 views

What do DBAs do? [closed]

Yes, I know they administrate databases. I asked this question because I'd like to get a further insight into the kind of day-to-day duties a DBA might perform, and the real-world business problems ...
4
votes
2answers
29k views

Entity-relationship model diagrams in Visio

I would love to create some database diagrams like this one with Visio but I can't find any templates at all for that kind of notation. The example I linked to was apparently made in Photoshop, which ...