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)

2
votes
2answers
407 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
68 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
197 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
25 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
45 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
89 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
47 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
36 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
121 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
53 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
40 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
52 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
49 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
82 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
50 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
24 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
56 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
45 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
52 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
41 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
33 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
52 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 ...
0
votes
2answers
79 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
72 views

Need Advice on Normalizing Database

I have a group of database tables that lack normalization. They all have a similar primary key called 'doc_index', which is either a PK by itself or part a composite PK. As you can see below, there ...
3
votes
5answers
275 views

Using Timestamp plus Integer as Primary Key

In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is: Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between ...
0
votes
2answers
64 views

How to change schema so that account_id reference is unique among 3 tables

Original image: Updated to have more-correct terminology and an 'is_debit' column: I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting. 'jackpots', ...
0
votes
2answers
28 views

Database design for tracking referral dollars and referred dollars

Dropbox has a great referral incentive program that gives a storage bonus based on whether a person is referred to Dropbox or refers others to Dropbox. What data base design is good for managing ...
1
vote
1answer
33 views

How to handle multiple hierarchies in a data model?

In Sql Server, there is a datatype called HIERARCHYID which helps with hierarchies. In our environment, we have patient data that needs to be classified under multiple hierarchies. Example 1: ...
1
vote
2answers
41 views

Dimension or Degenerate Dimension?

I am taking a course on data warehousing. Currently, I am reading through Ralph Kimball's Data Warehouse Toolkit. I understand that a degenerate dimension is a dimension key that is stored in a fact ...
1
vote
2answers
64 views

How to find usage of each object in other objects from a database?

I am with the task of taking an old SQL Server database and identify all of it's objects and their relationships, because the one who made it did not bother to use any foreign key, he just made all ...
0
votes
1answer
106 views

Database revision needed for sports events database

This is not a code review but more of a database schema review. I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. ...
0
votes
1answer
86 views

Inserting data into multiple tables

For my system, students are required to create a user account by completing a registration form (personal, contact and course details; also username and password). I need to collect registration data ...
0
votes
0answers
15 views

When there is a Type 1 and type 2 SCD in a dimension table, how are updates to the type 1 column handled after the type 2 column is updated?

Eg: ID Type1SCD Type2SCD FromDate todate 1000 100 ABC 01-JUL-2014 NULL Now, Type2SCD is updated ID Type1SCD Type2SCD FromDate todate 1000 100 ABC 01-JUL-2014 12-JUL-2014 ...
1
vote
1answer
36 views

pass data from deleted table to another

This is the situation: I have a table, with name TABLE_1 I would like delete TABLE_1 and then create another table with the same name and some of its columns. TABLE_1 has records, but I need to pass ...
0
votes
0answers
38 views

performance questions in database design

I need to describe a set of studies that are practiced to a patient, every patient have a "Clinical History", that are described in another table called "Clinical History Lines"(hclines) Every visit ...
1
vote
3answers
210 views

Heterogeneous ordered list of value

I have the following object model: class A { List<Thing> orderedListOfThings; } interface Thing { } class X implements Thing { int attr; } class Y implements Thing { String ...
0
votes
1answer
30 views

DBF corrupted database

I'm in great trouble here. I'm using Microsoft Visual FoxPro 9.0 on my Windows 7 machine. I had some DBF files which was created in MS Visual FoxPro. The files were undoubtedly valuable since they ...
0
votes
0answers
16 views

What is the optimal way to get a tabular view from an Entity-Attribute-Model table?

Following up on my previous question, I have created a table for storing measurements of different physical quantities. The schema looks like this: CREATE TABLE sensorreadings ( time TIMESTAMP, ...
0
votes
0answers
13 views

A better database model for level of product-information in service and maintenance environment

This is a tough question and rewriting it now for a couple of times. I have a question about a good, more solid way to design my database model about service and maintenance. My main problem are the ...
0
votes
0answers
41 views

Database design for large incoming data

I've been tasked to design a database to handle a large volume of incoming traffic from sensor readings, possibly 1,000s an hour when it's at full strength. Each reading will be relatively short - ...
0
votes
1answer
42 views

How to model when you could have many (thousands of) attributes to a table (product)?

We have a main table, let's say, Product. It has several general attributes like 'Product Name', Manufacturer ID, etc. As of now, all these attributes are implemented in the data model as columns to ...
1
vote
1answer
66 views

Handling a chat room in a database

I have been searching all day to find a better answer to my question but those I found was useful but do not critically analyse my question. I am building a chat room using php, mysql, jquery and ...
0
votes
0answers
60 views

How to share Users table between Clients and Customers?

I have a rental system where the system admin can add clients and their users. Each client's user can manage products, categories, orders etc., as well as their own customers. However, while ...
0
votes
1answer
32 views

Mixed search in multiple columns

I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance ...