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)

0
votes
0answers
33 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
0answers
14 views

Launching an RDS in a private subnet in VPC and access it from a web application [migrated]

I have setup a VPC which has two subnets. One is a public subnet (I have Internet Gateway attached to it) and the other one is a private subnet. My web application resides inside the public subnet and ...
1
vote
1answer
36 views

Unique constraint to enforce max one null per item

I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of ...
1
vote
0answers
48 views

Relational or non-relational database for a system to pass objects on?

hopefully this is the right place to ask this kind of question. I am working on a system that focuses on helping people give away things that they don't use anymore. So, an object typically has the ...
0
votes
1answer
26 views

How to predict the growth of a PostgreSQL database

I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so. I searched on Google, but I found only means to determine the ...
0
votes
0answers
32 views

DB design, when to split out into schemas

I am building a storage solution to store important themes/concepts referenced in books. At a high level the system will contain one or more catalogs and each catalog will contain one or more books. ...
0
votes
3answers
63 views

How to design database with january-december for every entry [closed]

Need help for designing my database, I have a table named transaction with columns: tr_id, item, item_cost, item_quantity, total and my problem is: I need to divide the item_quantity from january to ...
0
votes
0answers
13 views

How to test if a model is erroneous?

i am trying to realize why the following model is erroneous(or not). i am taking a database course and while i was studying I've found that model.
2
votes
2answers
29 views

Schema for storing historical data on shared identifiers

When user account is assigned an IP address, I create a table entry for the account/IP mapping. This data in the table is updated every 5 minutes. Right now I'm only keeping real-time data but I need ...
0
votes
2answers
46 views

What database should I use? [closed]

I am writing a log analysis program in bash. As part of it, I am compiling a list of known errors, so that I can search and reference those errors as part of the known list. I originally was going to ...
0
votes
2answers
34 views

Need Help maintaining multiple databases for a multi-tenant application?

i need some information about multiple databases for a multi-tenant application ? I am working on a project in which we are maintaining separate database for every company? The Companies Database ...
5
votes
2answers
38 views

How to enforce the structural constraints of rectangularly arrayed data?

(I'm particularly interested in answers that view the question below as a special case of the question: how should a RDBMS enforce structural constraints that are more specific than "one-to-many" and ...
0
votes
2answers
63 views

How to break table into two without losing performance?

According to http://stackoverflow.com/a/174047/14731, splitting away infrequently-needed columns frees up the cache allowing faster retrieval of the commonly-used columns. I've got a table whose ...
1
vote
1answer
49 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
0answers
52 views

How to handle many columns / variable schema?

I apologize in advance in case this question has been asked already. I'm working on revamping a reporting application used within my company. The requirements are: Support addition of new fields ...
2
votes
1answer
34 views

Hierarchy for dimensional model

I am trying to model a DW where I have many levels of geography (Neighborhood, District, City, State). I have several demographic data that should be included in the model. These data include count ...
0
votes
1answer
33 views

Max from 2 columns with priority date col then the seq col Oracle

I have a column xdate, column seqnumber, personID I want 1 record per personID, where it is the most recent xdate max(xdate) if there is multiple same max xdates i want it to look then for the max ...
0
votes
0answers
27 views

Minimize table joins in design

Probably didn't come up with the best title, but I'll explain what I'm trying to do. I need to be able to track gym members, trainers, workouts, and gym locations. I've created a simple example below. ...
0
votes
2answers
23 views

Null column for username and password when using Login with *social network*

I want to know what are the conventions for columns like username and password when your application supports login with a third party, like login with facebook for instance. Given the fact that when ...
3
votes
4answers
117 views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
0
votes
1answer
32 views

How do I choose primary keys for star schema BI data warehouse?

I am extracting sales turnover data from a Microsoft Dynamics Database into another SQL database that will be used by the BI tool of choice. This data is coming from G/L (general ledger) entry items. ...
0
votes
1answer
44 views

Schema for multi-account database

I'm building an app which will have accounts for organisations leading to organisation.myapp.com subdomains for their account access. I'm putting together the schema for it and I have a few ...
0
votes
0answers
18 views

Optimal layout strategies for Time-Series using Columnar storage

In the context of time-series and specifically columnar storage systems. Question: What are the processes or heuristics that are used to determine how best the columns of contiguous values be grouped ...
5
votes
1answer
371 views

Bad Design or Complicated Query?

Problem: For fun I am building a Database for lottery results to get some statistics off of. As of now I am only interested in a Pick 3 style lotto and might introduce more later. How would you ...
0
votes
1answer
101 views

Converted ERD into Dependency Diagram 1NF, 3NF

Thats the ERD. I'm studying for an upcoming test and having trouble getting my head around converting thes ERDs into dependency diagrams. This is from a previous exam that the Lecturer told us to ...
0
votes
1answer
55 views

Building a branched versioning model for relational databases

I am database designer and at my current project I'm implementing versioning capabilities required to concurrently edit rows of data in RDBMS. The project requirements says, that data editing sessions ...
0
votes
1answer
24 views

Establishing referential integrity in a generic table?

Current Schema: - DropDown: DropDownID PK, Name nvarchar(25) - DropDownOption: DropDownOptionID PK, DropDownID FK, Name nvarchar(25), Description nvarchar(155) ...
0
votes
1answer
36 views

modelling warehouse picking

What's the best way to approach modelling a warehouse picking system? To be precise, customers place orders which are processed by warehouse staff. A simple products and orders database isn't an ...
2
votes
0answers
47 views

Which is the better schema design for a voting system?

Imagine a q/a site or forum similar to stackoverflow. I'm trying to determine which of these two methods would be the better design for a voting system: (note: I would also like to keep record of all ...
7
votes
2answers
113 views

Should I add transitive foreign keys?

Simple example: there is a table of customers. create table Customers ( id integer, constraint CustomersPK primary key (id) ) All the other data in the database should link to a Customer, so ...
0
votes
0answers
20 views

Conditional value lists in MySQL

apologies in advance if the language I use here is insufficiently specific. Please let me know if there's anything that I can clarify. Does MySQL provide a means of creating conditional value lists a ...
1
vote
0answers
29 views

Identifying functional dependencies

I'm having trouble understanding the logic in use for identifying functional dependancies. Looking at the sample relation below, I understand fd1 - fd3. But when I look at fd4 and fd5, its logic ...
0
votes
2answers
40 views

What's the use of assigning foreign keys on MySQL [duplicate]

Aside from indexing the value of the foreign key, what is/are the advantage of assigning foreign keys to databases? I've seen a lot of databases that does not assign foreign keys, but is relational by ...
1
vote
2answers
51 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 ...
0
votes
0answers
25 views

Same column relational Id to multiple tables

Is it appropriate to use a column as a relational id to multiple tables? For example: referenceId - int relational id to various tables tableReference - String defining the table This way I can ...
1
vote
2answers
44 views

how to design a database's table to store mysql spatial data in a parent child relation?

I want to store locations and polygon data in a parent-child relationship. I am using spatial data in MySQL and Google maps for polygon. Examples: 1. World 1.1 US 1.2 China 1.3 Japan 1.4 South ...
1
vote
1answer
41 views

Should I add new column or new table

I have an existing table called exampletable1, now i have been given a new list of data called "medicines" it only have: male,female as its value. I'm having a problem whether to add another varchar ...
2
votes
2answers
409 views

Is there any way to do a SELECT query without any optimization in SQL Server?

I am trying to test the speed of SQL server queries. However, once I do the query once, it becomes a lot faster. The database is live so I can not make any changes to it, only to the SELECT query ...
0
votes
2answers
74 views

Composite Primary Key column order

How would you do the ordering of the PK columns in this case (Oracle 11gR2 Standard Edition): I have a database where I offer the same application to many users. I want to be perfectly sure that all ...
0
votes
1answer
43 views

Is this SQL schema normalized according to 3NF?

This schema is for a sports statistics/data app Note: Pink background = string/char/varchar Yellow background = int/tinyint Green background = dateTime bold column = PRIMARY key id_* = FOREIGN ...
0
votes
1answer
18 views

Spliting tables with *almost* same values

I have different kind of documents which have common fields (not all of them, but most) and I thought to put them in the same table and use ENUM to differentiate them. Then I realized that if have ...
3
votes
4answers
210 views

Best practice for a Table with private columns

I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ...
0
votes
2answers
27 views

Creating a database with only MySQL command line?

The linux server on which I'm attempting to create a MySQL database has many difficult to work with permissions. My supervisor has tried to install MySQL Workbench on the server, but has been unable ...
0
votes
0answers
22 views

Schema design for files

I'm implementing a logical schema for a (PostgreSQL) database that will need to keep track of large files that have been uploaded via a web interface into a local filesystem. At present the only ...
0
votes
2answers
52 views

Split date time, with only time, into minutes and seconds

I'm creating an Access database from an excel file that contains metadata about vhs tapes and DVDs. The person who created the excel file had the running times as a date/time field in minutes:seconds. ...
2
votes
1answer
110 views

Database design for a table with both bulk insert and single row insert

I have to design a table where records will be inserted in two ways. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
1
vote
1answer
48 views

3NF Decomposition - I'm not sure about this example

I am trying to make sense of an example of 3NF decomposition using the 4-step algorithm mentioned by Ullman here, but I'm not understanding what my lecturer is doing with the last step (or, worse, I'm ...
1
vote
1answer
38 views

Restrict two specific column values from existing at the same time

I have a PostgreSQL example table where at most one row that is not of type 'c' should be allowed. I would appreciate any help creating a constraint that will enforce this. CREATE TABLE example ( ...
1
vote
2answers
139 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 ...
0
votes
1answer
64 views

Text Storage and Database Design Optimization in a SQL Server Database

Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to ...