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.

learn more… | top users | synonyms (2)

1
vote
2answers
145 views

To normalize or not to normalize for few distinct values

Assuming in a Postgres database, you have a table called party, which can have less than 5 well-defined party_types such as 'Person' or 'Organization'. Would you store the party_type in the party ...
1
vote
3answers
182 views

Is this good/bad database design and why?

I am building a database for a small project. I don't have much experience so I am unsure, but I get the feeling I am not approaching this correctly. I have a system which allows users to create ...
1
vote
4answers
473 views

Unsure how to implement certain constraints and relations in this relational database schema

I am in the middle of designing the dB for school project- airline ticketing database.(Using MySQL workbench) So far, I came up with the following design: Here are couple things I can't figure out: ...
1
vote
2answers
406 views

Database Design - One table from many source(One to One)

I have a 'Text' table, the text table may came from different source, like Tweet, SMS, Email, etc. and each source have their own log, and Text is came from the source. so i came with some solution, ...
1
vote
3answers
2k views

Many-to-one Subselection in single query

I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2. To create a simple example, lets say T1 is Cars, ...
1
vote
2answers
119 views

Messaging Database - How to handle multiple recipients

So I am trying to develop a relational database (I'm using MySQL, planning on a Java front end) where I have 3 tables (People, where I store names and relevant personal information... and Messages ...
1
vote
3answers
83 views

Which of these two methods is standard when creating a 1 to many database relationship?

If I have a customer that can have many addresses, I can create an Address table with columns Street, Town etc. and CustomerId. Then I can insert multiple records to have multiple addresses per ...
1
vote
1answer
162 views

What is the meaning of this anomaly warning message?

I have run an analysis with SchemaSpy. The tool gave me a warning message for two tables. The warning message is: Tables with incrementing column names, potentially indicating denormalization ...
1
vote
2answers
167 views

Suggestion for storing a lot of simple data

I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date. The way I planned on laying out the database was as follows: 2 Tables ...
1
vote
3answers
3k views

Designing a Products database: One Products table or many separated by vendor?

In order to prevent the unwieldiness of managing product data spread across a number of excel spreadsheets, I have set up a Products database. My data is in spreadsheets because it was (somewhat) ...
1
vote
3answers
317 views

Mysql error while creating FK

here is structure of both tables category table CREATE TABLE category ( `catID` bigint(20) NOT NULL AUTO_INCREMENT, `title` varchar(60) NOT NULL, `created_at` timestamp NOT NULL DEFAULT ...
1
vote
3answers
177 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
2answers
286 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
2answers
414 views

Designing a database tables for the following scenario

I just got following entities Employee, Client and Company. Where Employee and Client have one to many relationship with Company ie A single employee may be mapped to attend to many companies and ...
1
vote
1answer
31 views

Does a Key-Value type table still needs a auto increment key?

Suppose that I have a very simple table to store variable-like data, like this: +-----------+-----------------+ | key | value | +-----------+-----------------+ | site_url | ...
1
vote
2answers
44 views

Why WordPress use a column prefix? [closed]

I'm trying to learn some basic database design through open source projects. And look into the WordPress database schema, the posts table for example, you'll find something like this post_author ...
1
vote
3answers
104 views

Postgresql: datatype for email address

What would be the right datatype to store email addresses in PostgreSQL? I can use varchar (or even text), but I wonder if there is a more specific data type for emails.
1
vote
1answer
143 views

Precautions when designing database schema if tables are going to have billons of rows

I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have ...
1
vote
1answer
1k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
1
vote
1answer
319 views

Modeling staff dimension in data warehouse

I need to write a data warehouse diagram. I've got a problem with staff area. I have to store a information about workers details like name, age etc., and workers job time and agreement details like ...
1
vote
1answer
538 views

How to handle huge table

We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it ...
1
vote
1answer
233 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
185 views

Choosing the right field data type in SQL Server 2008

I got a request to create a new table that will store certain information for customers. The definition of the table is somewhat irrelevant except for one field. This field will store ANSI characters ...
1
vote
2answers
298 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
3answers
232 views

How to convert MySQL Keyword to normal column

We thought of upgrading MySQL to new version 5.5.16. While testing the scripts with the new version, I had found one column Maxvalue is a Keyword in latest version. For this one, we need to change ...
1
vote
1answer
284 views

Where to save user data?

I hope this is the right stackexchange place to ask. Im really new using databases and I have a little question: Where to save the user data? I mean, if Im logged as "Foxandxss" on my app, and I want ...
1
vote
1answer
60 views

Data modeling with many attributes where few apply to each record [closed]

I'm pretty new to data modeling, so please excuse me if I've missed something obvious. I'm trying to decide on a schema (or possibly a DBMS) to work with some data where there are quite a few possible ...
1
vote
2answers
52 views

I don't know how to read many to many relationships

I'm reading this tutorial, and I have question about it. Look at this picture: Looking at Physical Many-to-Many Implementation I read the relationship this way: 'A user can be in zero or more ...
1
vote
1answer
104 views

Why do we not store names in a separate table?

Practically, I would never store names for customers in a separate table. But why not? It's a one to many relationship (a person can have one name but that name can be used by multiple people). ...
1
vote
3answers
124 views

One or several tables for attributes

I just spent some time discussing two alternative DB designs with a coworker and I'm not convinced. Neither of us is strictly DBA so we may be missing something. The overall goal is to create the ...
1
vote
1answer
2k views

Oracle database created whats next? Create Schema or Create Tablespace?

I'm a programmer that's being thrown to the DBA/Sysadmin island all by myself with a volleyball that I named Wilson. I'm trying to survive here. I'm supposed to create a database for an application. ...
1
vote
1answer
274 views

Table for optional parent/child relationship

Assuming we have the following table: Item, Parent, Child and Parent is the parent of child. The item can either belong to a parent or child and not both. I have other tables that are similar to ...
1
vote
2answers
486 views

History table design for supertype/subtype

I'm designing an asset management database that tracks IT hardware. I decided to use a supertype/subtype design. I'm at a point where I want to track history of changes for devices. I wanted to use a ...
1
vote
1answer
1k views

How to model entities in an entity relationship diagram?

I'm a beginner Database designer and wondering if anyone can help me understand this. Let's say we want to design a system that has, among other entities, an entity called Vehicle, and another one ...
1
vote
3answers
341 views

How to model a 1..1 Composition Relationship

We have a couple of entities/relationships of the following kind: Entity Event has a TimePoint The TimePoint contains time information about the event. It belongs to that event and cannot be used by ...
1
vote
2answers
250 views

Does this cause overhead ? Select name from User where user_name LIKE '%alex%'

I'm wondering if this statement can cause real overhead if many concurrent users send the same request to mySQL InnoDB storage engine. Let's say I have a table that has the user_name, and someone want ...
1
vote
2answers
401 views

Where can I find a good tutorial or book for various MySQL performance guidelines?

Can anyone refer me a good book or a small tutorial for various use cases for database design on MySQL for high load test/high availability scenarios. It should include the following: What are ...
1
vote
2answers
124 views

Where does a type specific field go?

Let's say I have table of cars and table of car manufacturers. Each car references one manufacturer. Furthermore, each car that has manufacturer x requires an extra field. Say, cars from that ...
1
vote
2answers
538 views

Database Design - Employee Clocking In and Out System

I want to develop Employee Clocking In and Out System (website). I am concerned two things: If staff forgot to 'Clock Out' from yesterday and they have 'Clock In' today, it should flag to the ...
1
vote
3answers
268 views

One to one relationship or another approach?

I need some addition thoughts on how to approach a database design I'm working on. The issue is I have three related items... * Clan (has many games) * Game (belongs to a clan, has many teams) * ...
1
vote
3answers
3k 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 ...
1
vote
2answers
670 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
2answers
257 views

Questions about Database Design for a Web Application

Consider a web application with 100,000 users. Each user can create blog posts. Assume each user has 100 entries. Would it be appropriate to store all the users in one table, with another table to ...
1
vote
2answers
303 views

Indexing of Data Warehouse tables?

Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because ...
1
vote
3answers
107 views

Many one-to-many relationships between two tables

In the process of creating a small database for a therapeutic boarding school with Access 2013, I have a problem when designing the relational database model. Two times at least, I ran into a ...
1
vote
1answer
115 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
1
vote
1answer
155 views

Concept, ER model, “employee tracking”

I have to achieve 3NF with following specifications: Database where I will determine priority skills for specific jobs and track the employees ability to complete them. Train employees. Example of ...
1
vote
1answer
158 views

Autoclose property resets to true after attaching db even if model is set to false

I am using SQL Server Express 2008 R2. The auto close property of a newly created database will be set to value same as model db. In my case I have set autoclose to false for model so that when I ...
1
vote
1answer
120 views

MS Access + SQL Server for 1160 users - Is it worth it? (Please Help!) [closed]

Our school in Delhi (India) wants to develop a professional and robust in-house database (SIS) that should handle vast amount of student information, including admissions, registration, re-enrollment, ...
1
vote
4answers
81 views

Better Indexing for MySQL?

Is it better to index each column individually with it's own key or to index multiple columns under the primary key? I've seen it done both ways in many different applications and I don't see a clear ...