Database design is the process of specifying the logical and/or physical parts of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.
2
votes
3answers
1k views
Sorting by DATETIME slower than by TIMESTAMP?
I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ?
Because I noticed that ordering by numbered keys is done ...
2
votes
1answer
362 views
Do I have duplicate key indexes?
CREATE TABLE relations (
object_id BIGINT(20) NOT NULL PRIMARY KEY,
object_term BIGINT(20) NOT NULL,
UNIQUE KEY link(object_id, object_term),
CONSTRAINT fk_term FOREIGN KEY(object_term)
...
2
votes
2answers
653 views
6th normal form, Recomposition query, efficient implementation
I found this paper written by Hugh Darwen for avoiding nulls in my database: Link, It describes how to implement databases in the 6th normal form so you can avoid nulls. The logic is described in the ...
1
vote
2answers
99 views
Designing an Accumulating Snapshot with Repeated Groups, need advise
Situation
I need to create a pie chart that has to be able to be represented at the following levels: District, Region, and School.
At the student level it has to show the trend of the student over ...
1
vote
3answers
130 views
Should I create a separate database table for temporary users
I have a user table that controls access to a website. We currently have a few hundred users and this might eventually grow to a few 10,000s. We have a requirement to allow in "temporary" users. These ...
1
vote
1answer
418 views
User system database design
I'm designing a database for a new project and I'm looking for some feedback on the design before I start to implement/test. It basically consists of users, events, user profiles and a messaging ...
1
vote
2answers
1k views
how to Export a document contain tables name, columns names and column description from SQL Server 2008?
I created a db in sql server 2008 and I wrote for some columns description to describe them, What I want now is to find a way to export these tables design including the description for each column ...
0
votes
1answer
103 views
database normalization and optimization for postOffice DB
I am designing a web application for a post office.
Performance in database is critical for them.
I have designed the below structure for the post database, is there any flaw in my db structure which ...
2
votes
2answers
412 views
Logical schema development and migration tool for different physical schema available including triggers and stored procedures?
We are planning to support for our application another rdbms (oracle).
At the moment we are design our database schema with the MySQL-Workbench. For every new database version we have
schema ...
3
votes
3answers
240 views
Handling attributes that are time-variant in a Datamart
I have looked through the entire list of sites, and this is I think the best match. This is not really about database administration, more like database design. Please excuse me and point me to the ...
0
votes
1answer
83 views
What model design supports both data store backed and free form entries?
I have an application where users are tracking a workflow around producing a derivative work from an existing Book. The derivative work, we'll call "BookDerivative" must have a book that it relates to ...
0
votes
1answer
121 views
What are the first necessary actions needed to help secure a database's schema & data? [closed]
This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions.
It seems like there should be a good checklist out there for all to agree on ...
4
votes
1answer
95 views
Possible pitfalls of condensing 2 SQL Server instances into 1
I currently have 2 SQL Server 2000 database instances (each running on a different physical server), each containing around 12 databases (not all are active). We are planning an upgrade to SQL Server ...
3
votes
1answer
412 views
Webapp & MySQL: Row Level Security
I am trying to emulate row level security on a webapp I am developing using MySQL.
Using this method:
Creating a database with the the required tables where the data pertaining to all the users will ...
4
votes
3answers
426 views
Single versus Multiple databases
I am working on a hospital management system using DB2 with pureXML.
Is better to use a single database for all the data (patient, doctor details, appointments, history, medicines, etc) or to use ...
1
vote
2answers
254 views
serial field issue
Q:
I use a serial as a datatype for my primary keys in my tables.
I insert a lot of data then truncate these data one after one many times to test the data entry.
My question is:
Is there any ...
2
votes
1answer
172 views
In DB2, what are the alternative ways of evaluating an index's (or several indexes) benefit?
I'm trying to evaluate different approaches to analyzing the benefit of changing the set of indexes that are available. If I pick some representative queries from my workload, I can run EXPLAIN and ...
1
vote
1answer
100 views
Is maintaining a single database an issue when the underlying project becomes larger?
I have seen this related post but it was more focusing on the amount of consumers for the databases, which is not my only problem.
I have a situation, where I am taking part of the design of a new ...
4
votes
2answers
349 views
Constraint many to many table between two child tables
New to this exchange site so I hope my question isn't inappropriate.
Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently.
One table has a ...
0
votes
3answers
339 views
How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS?
I want to design a db schema concerning a schedule.
I. I can use all fields in the same table, for example:
id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su
1 | 1 | ...
1
vote
2answers
480 views
Can we decompose a schema into BCNF when it is not even in 1NF
Given a schema r(A,B,C,D,E,F) and a set of functional dependencies
A --> BCD
BC --> DE
B --> D
D --> A
As we can see the schema is not even in 1NF as can be read here, now the question is can we ...
1
vote
1answer
158 views
Design Tagging module in the database
I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By ...
4
votes
1answer
198 views
Speed inserts in database with a table with +47 million data per each year, containing more than 10 years?
I'm moving my database to mysql but there is one table that is pretty big (more than 47 million rows for each year) and it takes years to insert all the data.
I already tried everything I found in ...
5
votes
3answers
1k views
One Big Database vs. Several Smaller Ones
We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g.,
...
0
votes
1answer
200 views
Oracle: Extract Graphical Database Representation [duplicate]
Possible Duplicate:
Oracle Creating ER Diagrams and Data Dictionaries
How can I extract a graphical (schema) representation of an Oracle database (tables and their relationships; with ...
2
votes
5answers
350 views
Pros and cons of having one table per entity vs. having a shared table with an extra column indicating the entity type
I am working on a database design and am wondering about the pros and cons of two setups. I am going to collect data about two types of entities, lets say cars and boats. The structure of the data ...
9
votes
3answers
1k views
Best way to design tournament database
I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase.
I have created a mockup ...
1
vote
2answers
254 views
Best type to use in a composite key when one of the values varies in type
I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table
BlogId Url ...
1
vote
2answers
213 views
Reporting Database Considerations
We have a very large database that we want to write almost real time. We want to take data and move it to another instance of the database on top of which will be a GUI and MI tool which will be able ...
1
vote
1answer
283 views
Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?
I have bank transaction table with columns
Division
Brcode
TrDate
ProjectCode
Accountnumber
EffectiveDate
DrCr
Amount
EndBalance
In the other table the Brcode itself is unique and is assigned under ...
2
votes
1answer
252 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 ...
4
votes
2answers
306 views
Recommended model for application level event logging
I am going to be storing application level events (user A added thing, user B updated that etc) for both public and private consumption.
Below is an oversimplified model of the tables and their ...
2
votes
2answers
308 views
What are the pros and cons of using my customer codes as a primary key?
I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other ...
6
votes
3answers
700 views
How to normalize Time Sheet data?
I am having a terrible time finding an appropriate way to store this data in a database.
The current excel (simple) version looks something like this:
http://i.stack.imgur.com/BEZOw.png
Staff and ...
4
votes
3answers
1k views
Are there any free online collaboration database schema tools?
I am interested in a free and simple MySQL tool which can offer online collaboration functionality in creating database general schema. Do you know any?
4
votes
3answers
2k views
PostgreSQL designing tool
I am about to design my first database which is going to run with PostgreSQL. I am used to the magnificant tool called MySQL Workbench. It is useful and it looks good which I kind of expect from a ...
8
votes
2answers
605 views
Why can't I use NEWSEQUENTIALID() as the default value for my column?
I'm trying to create a table in Management Studio and after reading about the new (from SQL 2005 on) NEWSEQUENTIALID() function, thought I'd give it a go.
This is what I'm doing:
But it's not ...
6
votes
1answer
361 views
Concept of Schema in PostgreSQL
I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design.
Why should I use it?
Can it affect me in future if I decide to not think ...
1
vote
1answer
62 views
Design for a Testing system
I want to redesign a part of my system. I got the following objects:
Site:
id
Name
Site Version:
id
idSite
Name
Task:
id
idSite
Name
Test:
id
idTask
Name
TestResult:
idTest
idSiteVersion
...
4
votes
2answers
534 views
Best way to handle dates prior to 1000 A.D. in MySQL?
I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000.
Is there a way that would be more convenient than either ...
-1
votes
2answers
241 views
Diet and exercise schema
Pardon the brevity but: I am currently working on a project which requires users to create a 12 week diet/exercise regime. I basically need to store 3 meal options for each day which can be made up of ...
2
votes
2answers
386 views
Can a 2NF relation be M:M?
I have recently learned about 1NF, 2NF, and 3NF. I understand the definitions and the differences. I also had learned earlier about removing M:M relationships from a conceptual model by using ...
4
votes
3answers
378 views
Scalable table structure for periodically updated stats that get aggregated over time
I receive statistical data every 30 seconds that I want to store in my database so that I can analyze later. For example, every 30 seconds I could receive the number of oranges sold at a store in the ...
0
votes
1answer
231 views
How could I handle heavy load and consistency for product inventory in online retailers? [closed]
I have posted that question on Stackoverflow and they suggest me to repost my question here, as it would more likely to get help from a DB community (regarding this issue).
Hope you guys don't mind.
...
3
votes
3answers
984 views
indexes, foreign keys and optimization
Not a DBA so pardon my ignorance...
I have a table with 20 fields of which 8 are foreign keys. Mysql has by default added an index for each foreign key. Table has about 100k rows.
When retrieving ...
8
votes
2answers
469 views
INT or CHAR for a Type Field
What is the best design for a table, a Type field that is of int or char(1)? In other words, given this schema:
create table Car
(
Name varchar(100) not null,
Description varchar(100) not ...
15
votes
3answers
4k views
Advantages and Disadvantages to using ENUM vs Integer types?
Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled.
Is it better for this column's data type to be an int/bool (1 or zero) or to ...
3
votes
1answer
169 views
In terms of databases, what does 'roach motel' mean?
Recently Larry used it in one of the OpenWorld keynotes.
Wikipedia says
In computers, sometimes as slang use:
Sometimes used to refer to a proprietary file standard -- "you can check ...
0
votes
1answer
327 views
What are the other types of database architectures? [closed]
Oracle, for example has a typical client server architecture, and a memory cache (SGA) with some background processes.
This is one way of designing a database. Are there databases out there which ...
4
votes
2answers
280 views
Best way to design this mileage table?
I will be populating a Miles Per Gallon (MPG) table. It's coming from an odometer source.
It's currently set up as so:
id (primary_key)
, truck_num
, start_date
, end_date
, ...
