For questions about database design.
38
votes
12answers
9k views
Is it a bad practice to store large files (10 MB) in a database? [closed]
I am currently creating a web application that allows users to store and share files, 1 MB - 10 MB in size.
It seems to me that storing the files in a database will significantly slow down ...
36
votes
12answers
2k 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 ...
23
votes
10answers
1k views
How do you organize highly customized software?
I'm working on a large software project which is highly customized for various customers arround the world. This means that we have maybe 80% code which is common between the various customers, but ...
20
votes
6answers
830 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 ...
19
votes
12answers
1k views
How To Deal With Terrible Design Decisions
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. ...
19
votes
6answers
2k 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 ...
17
votes
4answers
950 views
Best Practices for Redesigning a Database
I'm aware of some general best practices when designing a database for an application, but what about redesigning?
I'm on a team tasked with re-designing an internal business application, though ...
16
votes
7answers
590 views
De-facto standards for customer information record
I'm currently evaluating a potential new project that involves creating a DB for typical customer information (userid, pwd, first & last name, email, adress, telfnr ...). At this point, ...
15
votes
9answers
1k 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 ...
14
votes
5answers
298 views
Should my multi-server RDBMS or my Application handle database Referential Integrity?
Should items like Foreign Keys, Constraints, Default Values, and so on be handled by the database management system (in this case, MS SQL 2005) or the application? I have heard opinions from both ...
13
votes
5answers
884 views
Is it the job of programmers to design the database?
I've been a programmer for the past six years. Throughout my career, I have worked on many web applications.
Most of the time, when a database was needed, it was given to us (the programmers) or we ...
13
votes
7answers
1k 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 ...
13
votes
6answers
572 views
What do DBAs do?
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 ...
12
votes
6answers
1k views
In plain English, what is a database index most similar to?
Setup: Suppose you are teaching an introduction to Databases class, the students are CS students that have a working knowledge of tree structures, how they can speed up searches, and have probably ...
12
votes
10answers
1k views
How do you approach database design?
I am primarily a web developer and I have a couple of personal projects which I want to kick off.
One thing that is bugging me is database design. I have gone through in school db normalization and ...
12
votes
4answers
2k views
Should I use one database per application or share a single database amongst multiple applications
I have multiple applications some that use data from the same sources.
Is it best practice (or what are the pros/cons) to:
leave the data in databases shared by multiple applications
saves space ...
12
votes
9answers
4k views
Should I use multiple column primary keys or add a new column?
My current database design makes use of a multiple column primary key to use existing data (that would be unique anyway) instead of creating an additional column assigning each entry an arbitrary key. ...
12
votes
4answers
264 views
Can I use the database structure of an open source project?
I found a database structure for a CMS system and I want to copy it with the EF that this database is done in, is it OK to copy the database structure of open source software which is under GNU v2 ...
11
votes
10answers
812 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 ...
11
votes
3answers
453 views
When accessing/manipulating complex data, is it better to store it in many small pieces or one large chunk?
I am building a web app that manipulates fairly complex data: guitar tabs.
As a reference, guitar tabs look like this:
...
11
votes
2answers
328 views
What are the advantages of separating 'result' from 'status'
Let's say you have some automated processes that generally go through the following states;
scheduled - initiated - validating - executing - completed
On top of that these processes can prematurely ...
10
votes
7answers
566 views
Are there examples of non CRUD approaches?
I'm a programmer but also have worked as an archivist. As archivist it's a lot about keeping data.
I often get into arguments with colleagues when it comes to operations on data. I don't like the U ...
10
votes
8answers
852 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 ...
10
votes
2answers
475 views
General thought process for “How would you build this website/app” interview questions
I've collected a bunch of interview questions like "Describe how you would design a photo album application", "Describe how you would design this particular feature of this particular website" (e.g. ...
10
votes
1answer
2k views
When should you use a document vs relational vs graph database? [closed]
For the purposes of discussion let's consider a FourSquare scenario.
Scenario
Entities:
Users
Places
Relationships:
Checkins: users <-> places, many to many
Friends: users <-> users, ...
9
votes
3answers
975 views
Should I store False as Null in a boolean database field?
Let's say you have an application that has a boolean field in its User table called Inactive.
Is there anything inherently wrong with just storing false as null? If so can you please explain what the ...
9
votes
4answers
2k 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 ...
9
votes
1answer
664 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. ...
9
votes
5answers
783 views
Is there any reason to use VARCHAR sizes rounded to a 128/256/4096 byte offset?
In database schema's, I often notice VARCHAR sizes are rounded to the byte offsets 128/256 or 4096. I've done it before as well, and the idea behind it was probably something with efficiency.
...
9
votes
1answer
501 views
Classless tables possible with Datamapper?
I have an Item class with the following attributes:
itemId,name,weight,volume,price,required_skills,required_items.
Since the last two attributes are going to be multivalued, I removed them and ...
8
votes
8answers
1k views
Copying a competitor's database schema?
I am going to be releasing some software soon which will require users to run a local database. There is a competitor in the space that is doing the same thing and they have a pretty sophisticated ...
8
votes
7answers
656 views
Design Parts DB
I'm developing a tool that handles (electrical) parts. The parts can be created, viewed, modified, deleted, grouped and so on...
In order to make this question useful for future visitors I like to ...
8
votes
6answers
8k views
what exactly do database programmers do?
Please take no offense, this is a genuine doubt I have.
Every time I read about Oracle programmers etc, I get this doubt. I am unable to understand what exactly they do. From my understanding, ...
8
votes
7answers
745 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 ...
8
votes
6answers
839 views
Favoring Immutability in Database Design
One of the items in Joshua Bloch's Effective Java is the notion that classes should allow mutation of instances as little as possible, and preferably not at all.
Oftentimes, the data of an object is ...
8
votes
2answers
286 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 ...
8
votes
8answers
1k views
How should repetitive calendar tasks be stored in the database?
This is for a small personal project for micro-management. Basically, I store tasks in a SQLite3 database that looks like this:
id INTEGER PRIMARY KEY AUTOINCREMENT
label TEXT
deadline ...
7
votes
2answers
864 views
Should we do entity-relationship modeling or object-oriented modeling first?
When building an application from scratch, should I start with the object-oriented (OO) model or the entity-relationship (ER) model?
7
votes
5answers
2k views
Is there a tool that creates database design diagrams? [closed]
I'm looking for an application to create simple diagrams for database design. I'm not looking for anything that will help create the databases, just a simple tool for a design report.
I have tried ...
7
votes
5answers
492 views
Predicting advantages of database denormalization
I was always taught to strive for the highest Normal Form of database normalization, and we were taught Bernstein's Synthesis algorithm to achieve 3NF. This is all very well and it feels nice to ...
6
votes
5answers
309 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
...
6
votes
6answers
624 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 ...
6
votes
6answers
693 views
Agile methods and Databases at the start of the project
New to agile and I'm not sure how to start. The idea is to create small parts of the project in sprints. However the project I'm working of requires a database and the database must be almost ...
6
votes
3answers
2k 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 antipattern.
But I'm wondering, if is it that wrong ...
6
votes
4answers
538 views
Add an ID in every table even if it's not needed?
Is this good or bad design to add, by default, ID field in every table in a database, even when you don't see currently to usage of this ID (For instance in a MxN table) ?
6
votes
1answer
687 views
self referencing tables, good or bad?
Representing geographical locations within an application, the design of the underlying data model suggests two clear options (or maybe more?).
One table with a self referencing parent_id column
uk - ...
6
votes
5answers
213 views
Storing a re-orderable list in a database
I'm working on a wishlist system, where users can add items to their various wishlists, and I plan to allow users to re-order the items later on. I am not really sure about the best way to go about ...
6
votes
4answers
215 views
Where ORMs blur the lines between code and data, how do you decide what logic should be a stored procedure, and what should be coded?
Take the following pseudocode:
CreateInvoiceAndCalculate(ItemsAndQuantities, DispatchAddress, User);
And say CreateInvoice does the following:
Create a new entry in an Invoices table belonging to ...
6
votes
5answers
999 views
Configuration data: single-row table vs. name-value-pair table
Let's say you write an application that can be configured by the user. For storing this "configuration data" into a database, two patterns are commonly used.
The single-row table
CompanyName | ...
6
votes
3answers
375 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 ...
