Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,227
questions
38
votes
5
answers
60k
views
In SQL, is it composite or compound keys?
About SQL (Computing/Databases):
When we have two or more fields in a table that, together, identify its records uniquely, what's the proper way of calling them? Composite or compound keys?
I've ...
- 483
8
votes
2
answers
655
views
Am I violating any NF rule on my database design?
I am a newbie on creating database... I need to create it for my recruitment web application.
My application needs to schedule screenings, exams and interviews of applicants and save result in the ...
- 181
5
votes
3
answers
1k
views
Suggestion required for Large Database Optimization and performance
We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from ...
- 337
8
votes
3
answers
2k
views
Suggestion for Large SQL Server Database Design
We are creating a database in MSSQL 2008 R2 Standard where we will be storing a large number of records. We estimate 200 million+ records in one table annually and we are primarily INSERTing with very ...
- 337
2
votes
1
answer
651
views
Which NoSQL database(s) should be used for advertising platform? [closed]
What NoSQL database or combination of databases best meets these criteria?
Very fast writes and reads
Persistent data that won't get lost in a crash
Plays nice with PHP
Works well in the cloud (EC2)
...
- 131
5
votes
2
answers
2k
views
Best design for adding a folder/file relationship
I was given a database that has Projects and Media_files. They were in a simple 1 to many relationship.
I now need to add Folders to Projects. Media_files can be placed in Folders, and Folders ...
- 153
2
votes
1
answer
2k
views
Design of fact table(s) and dimensions tables for data warehouse
Hi i'm newbie in Datawarehousing, how would you model this in a Data Warehouse:
i wish design the Data Warehouse which give the answers of statistics relating to a baseball league
For players
in ...
- 121
9
votes
3
answers
621
views
Database design advice
I'm designing a database for our sales team to use as a quick job quoting tool. I would like some feedback on a particular aspect of the design.
A quote is basically built up by selecting a list of ...
- 539
5
votes
1
answer
291
views
What databases or styles of database can use git (or another dvcs architecture) to manage cross-site replication?
I'm looking for a database software package for a dictionary (like the hunk(s) of dead tree that you might have somewhere at home). I would like the database to
scale well to a million key-value ...
- 151
1
vote
1
answer
138
views
Are there situations where queries are formed geometrically?
The topic came up in this other question. We were discussing sign value on a primary key having any significance.
If there are good times to use geometry in building queries the sign value could be ...
16
votes
3
answers
9k
views
What are negative keys used for?
Somewhat new to using standard SQL databases (currently working with MySQL mostly) I haven't run across many usages of this as of yet.
When and why is it useful to have negative (or rather signed) ...
3
votes
1
answer
383
views
MySQL data modeling help
I've been struggling with this model for weeks, going in circles from one type of solution to another. I'd like some advice on the best approach.
One or more organizations can sponsor an event or ...
- 183
3
votes
1
answer
191
views
Mysql constraint problem
I'm using MySQL5.5 and for this question I have 3 simple tables t1, t2 & t3 designed like this:
+------------+ +------------+ +------------+
| t1 | | t2 | | t3 |
...
- 539
6
votes
3
answers
20k
views
Relational Design - Multiple tables into one foreign key column?
Take a table that tracks meat purchases. It has a "meat_id" foreign key column to indicate what type of meat the purchase was.
But, the different types of meat are unique in some way (such as USDA ...
- 255
18
votes
5
answers
24k
views
Finding empty columns of a table in PostgreSQL
What query would return the name of the columns of a table where all rows are NULL?
- 181
5
votes
2
answers
636
views
How do I store data with a query that's approximated?
I'm trying to find a way to store my data with fast access (better than O(n)).
My database consists of data (4096 byte strings) that represents some information about some items.
The problem is, that ...
- 163
0
votes
1
answer
2k
views
Entity-Relationship Diagram for a scenario involving Books, Authors and Countries
Certainly a noobish question, but I'll ask it anyway. Perhaps someone else will be thankful. ^^
I am getting warmed up with MySQL and I try to understand the concepts of creating well structured ...
- 377
6
votes
4
answers
864
views
Purpose of IDs in a User Table Database
In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
- 163
46
votes
12
answers
7k
views
How could DBAs be more 'programmer friendly'?
The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing ...
- 39.7k
1
vote
1
answer
393
views
Versioning a dataset in an RDBMS using initials and deltas (xpost from SO)
I'm working on a system that mirrors remote datasets using initials and deltas. When an initial comes in, it mass deletes anything preexisting and mass inserts the fresh data. When a delta comes in, ...
- 113
82
votes
10
answers
14k
views
What are the arguments against or for putting application logic in the database layer?
NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting ...
- 1,469
170
votes
12
answers
113k
views
Should binary files be stored in the database?
What is the best place for storing binary files that are related to data in your database? Should you:
Store in the database with a blob
Store on the filesystem with a link in the database
Store in ...
- 39.7k
2
votes
2
answers
3k
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 ...
- 197
7
votes
3
answers
7k
views
When to apply normalization in database design
Hello database administrators.
Recently, I have been reading about normalization in order to improve my database design skills. However, I am a bit confused about when to apply this technique. Prior ...
- 71
2
votes
2
answers
2k
views
SQL datatype choice for flagging a read/ unread message?
I have a table where I want to track Boolean type operations. in addition, a business requirement is that I know the date of the action and not just the Boolean value.
for example if a message is ...
- 2,032
3
votes
1
answer
307
views
What's the best paradigm or design pattern for exception handling in a mission-critical DB driven web application?
I want to design a bullet-proof, fault tolerant, DB driven web application and was wondering on how to architect it.
the system will have a asp.net UI, web services middle tier and SQL2005 back end. ...
- 2,032
2
votes
2
answers
848
views
Determining sizes for Separated Partitions of programs, data and logs in SQL Server
As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server.
Question is, what is ...
- 185
11
votes
3
answers
2k
views
Where does the magic column "name" come from?
I got this by accident:
db=> select name from site;
ERROR: column "name" does not exist
LINE 1: select name from site;
^
db=> select site.name from site;
name
--------------...
- 885
9
votes
1
answer
112
views
Which schools are known for graduation level database research?
Which schools across the world are best known for research being carried out in database field in recent times?
user1497
2
votes
2
answers
2k
views
Best index strategies for read-only table?
I have a table in SQL Server which has following characteristics:
will contain about 1.2M records.
first is empty, will be inserted in batch many times (precisely, 64 times), 20-100k records/each
the ...
- 133
7
votes
2
answers
2k
views
Is a “‘Junction’ Table” the same as a Weak Entity?
Is a “‘Junction’-Table” basically just the table that sits between two entities that have a many-to-many (M-M) relationship?
- 1,702
3
votes
3
answers
1k
views
Priority Inbox Database Design
I was just wondering, what would be the table structure for a messaging system similar to Gmail's priority inbox?
If you're unfamiliar with the system, it prioritises messages based on the type of ...
- 33
9
votes
4
answers
21k
views
Database design for a time tracking application
I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user to enter for each day of the week the amount of time he ...
- 193
9
votes
5
answers
12k
views
What is the most efficient way to alter a column definition in a table with millions of rows
I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple
alter table Table1 ALTER COLUMN Column1 XML NULL
but it takes forever. So here are my ...
- 367
3
votes
1
answer
374
views
In a recruiting website, should I force a choice between one table for jobseekers and employers or 2 separate tables?
I'm working on a recruiting web site and I was wondering what would be best between:
A User table, the employer accounts are linked to a company
(therefore when companyID is null then it's a job ...
- 1,873
15
votes
5
answers
129k
views
Matching single column against multiple values without self-joining table in MySQL
We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data:
user_id ...
28
votes
7
answers
16k
views
How do long columns impact performance and disk usage?
In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on.
In reality, how much does it really matter? How ...
44
votes
2
answers
121k
views
Database model with users, roles and rights
I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user.
Below is ...
- 543
26
votes
7
answers
21k
views
Storing IP address
I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column?
Should I support IPv6 as well? If so, what is the ...
- 515
9
votes
4
answers
20k
views
How to design a relationship database table to store friendship relationship?
I want to design a table to store friendship relationship in my web project
It should satisfy at least the following 4 conditions:
who send the add-friend request e.g.(if A TO B then this column ...
- 191
23
votes
6
answers
27k
views
Is there any good tool for making Database Design and Prototypes? [closed]
I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good ...
- 32.6k
19
votes
7
answers
7k
views
Is polling the only way for updating app's data from a database?
An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the ...
- 1,555
1
vote
2
answers
860
views
How wrong is my web app modeling schema?
So, I'm about to write a quite simple web application, I am currently working actively on the database schema, and I need help to avoid major drawbacks. I've been programming (php) for a few years now,...
- 113
11
votes
5
answers
8k
views
Are there opensource hierarchical database management systems [closed]
I was looking for a hierarchical database management system and the only one i came across was IBM's ims.Are there opensource systems that one can use?.
- 295
23
votes
6
answers
10k
views
Needing A Database Design Book [closed]
I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex ...
11
votes
2
answers
4k
views
How do I implement a database/table as a stack
I have a state-machine which needs to push/pop some file-names for different users. I would traditionally use stacks as the choice of data structure, but this needs to be done using a database since I ...
- 257
7
votes
2
answers
19k
views
MySQL Database Schema for Train Timetables
I am interested in what a MySQL database schema would look like for a train timetable.
Generally this is presented in table format as the final result.
Train No. 11111 22222 11111
Train Day ...
- 99
1
vote
1
answer
238
views
How to design databases? [closed]
Describe the process in steps, please. What tools do you use for it?
- 113
2
votes
1
answer
916
views
MySQL Database Schema for Volleyball
I am developing a database for the recording of Volleyball results. I have a fixture database that tells me who plays who in each round.
Teams Table, say IDs 1,2,3,4
Fixtures Table, Round No.1, Team ...
- 99
12
votes
3
answers
532
views
How-to implement an entity with an unknown maximum number of attributes?
I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
- 223