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
14 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
16 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 ...
1
vote
3answers
38 views

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

My boss and I are having a difference of opinion about whether or not to use foreign keys in a MySQL/InnoDB database. I'm for using them for enforcing RI and taking advantage of ON DELETE CASCADE and ...
0
votes
1answer
16 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
24 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
12 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 ...
4
votes
1answer
294 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
51 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
0answers
19 views

Correct Schema Design for form builder?

I'm attempting to build a form builder app that allows users to: signup build a form store the data from that form into this db. I have designed the following schema: ...
0
votes
0answers
29 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
22 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
33 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
36 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
104 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
29 views

Tinder database structure? [on hold]

For those of you familiar with the way Tinder works, do you happen to also know the underlying database structure behind it? How it manages to store all the information, the memory efficiency, etc. ...
0
votes
0answers
17 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
27 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
36 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 ...
0
votes
2answers
41 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
18 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
38 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
40 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
405 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
65 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
16 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
193 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
24 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
21 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
43 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
81 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
46 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
32 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
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 ...
0
votes
1answer
51 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 ...
0
votes
0answers
38 views

Struggling with normalisation (database design critique)

I'm relatively new to database design and have been reading up a lot on best practice, and I'm starting to design a schema for a new application that will be developed and I am struggling with a few ...
2
votes
2answers
51 views

Give every user 3 default 'bookmark' entries

I've got a table users and a table bookmarks. The table bookmarks obviously contains pages the user wants easy access too. But, there's a new requirement now: every user gets 3 unmutable bookmarks. ...
2
votes
1answer
42 views

Table design for child tables with multiple many-to-many relationships

We are working on designing tables for a new Networking application, and have been having some problems with finding the best design for the top level tables. Network is the top level table. Under ...
3
votes
1answer
77 views

For alignment optimized table is bigger than original table - why?

In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
1
vote
1answer
46 views

How to design a database that tracks sports player's positions

I'm building a database for a sports (ultimate) tracking program. Basically, the "raw data" for the program will be the positions and IDs for all the players on the field at one time. But the way I'm ...
1
vote
1answer
23 views

Relying on expected data

I'm relatively new to Database Design and I wanted to know the disadvantages to a particular decision I have made in the design of a database for a project. I don't think it will have an effect on the ...
0
votes
1answer
51 views

Database design suggestions for a data scraping/warehouse application?

I'm looking into the database design for a data warehouse kind of project which involves a large number of inserts daily. The data archives will be further used to generate reports. I will have a ...
0
votes
1answer
19 views

Optional oneToOne relations and criteria field

Introduction: Visitors can fill reports on my website. Those report can either be filled by a legalPerson or a naturalPerson. Constraint: I don't like empty fields and I would like to normalize as ...
1
vote
0answers
40 views

Conceptual model vs Logical model vs Physical model

I'm doing a work about database, and now I need to show three different images, one image with the conceptual model, other with logical model and other with physical model of a database. I'm looking ...
2
votes
1answer
50 views

Conditionally join table

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me. I have a master table which is then joined to another table ...
0
votes
1answer
40 views

Parent/Child table design?

Lets say I have a table to hold Tasks and then I have a table to hold Sub Tasks, is it better to break Tasks and Sub Tasks into separate tables or keep them both in the same table with a possible ...
1
vote
2answers
31 views

Store n:m data with most n:1 tuples

I have these tables: create table data ( id int identity not null, ref int not null ); create table reference ( refid int not null ); with ~2 million rows in data and ~200k rows in ...
-1
votes
1answer
44 views

Database Design for classifieds site [closed]

I'm creating a classifieds site where there are many categories and subcategories. 1) Each category has different fields. 2) Each listing has one or more images I need help with the database design. ...
0
votes
2answers
43 views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
0
votes
2answers
34 views

Mysql replication with c

I have huge database on MySql and It's become more and more huge. So, Now I am facing performance issues with my database. Some of tables has millions of records in it. like my login detail table ...