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)

7
votes
5answers
1k views

Database design: Dividing multiple identical tables, good or bad?

I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them. I have seats at a theater, the seats are divided into 4 main areas ...
3
votes
1answer
1k views

Db design for historical option data (~200GB)

I need some help determining how to best design my tables. I have about 200GB worth of data I wish to place in a table (historical option price data). The table looks something like: String symbol ...
1
vote
1answer
94 views

Game database- many to many relationship

I am designing a simple relational database about a game that can be played by 2 players each time. I have these tables: Player, Game, PlayerPlaysGame, Payment. I recognize there is a ...
15
votes
11answers
1k views

Why are constraints applied in the database rather than the code?

Why are constraint applied in Database? Will it not be more flexible to be in code? I'm reading a beginners book on implementing databases, so this is a beginner's question! Let's say I design a ...
3
votes
2answers
449 views

How to implement posts with “seen by” like facebook?

Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me. And if I see a specific post inside a group thought a url ...
-1
votes
2answers
73 views

Column or new table [closed]

I am working on a student management system for a private school. In the table "student" I need to indicate which public school the student came from. Can I just keep this information as a column ...
0
votes
2answers
366 views

“Variant” values structure and their use for multiple columns

note to the edit: the question originally was only about the opportunity of using a single "variant" structure for multiple columns, but all initial comments asked clarifications about the actual ...
8
votes
1answer
2k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
1
vote
3answers
168 views

How to handle update or insert situations

From time to time my stored procedures looks like create procedure handle_data @fk int ,@value varchar(10) as begin if exists (select * from my_table ...
5
votes
1answer
3k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
0
votes
3answers
112 views

Column partially determining accepted values of another column, should be somehow normalized?

I have a table that describes a set of properties (yes it's metadata, in this case there's a valid reason to have it in the database); among other things I tell the type of the data that can be ...
7
votes
3answers
833 views

Should I separate frequently updated columns?

I have a users table that contains users' information and a column named credits that is frequently updated. On index page I'm showing list of users with basic user information but I don't need ...
0
votes
1answer
242 views

Trying to create a Data Model

It may sound stupid but it's my first experience ever with databases. It's a simple database for a car accessories store. I was hoping when making a sale on a certain item it effects the quantity of ...
1
vote
1answer
772 views

Unique index for subquery with RANK() function

I have the following tables: CREATE TABLE Revision ( RevisionId INT PRIMARY KEY IDENTITY, UserName NVARCHAR(256) NOT NULL, DateTime DATETIME NOT NULL DEFAULT GETDATE() ) CREATE TABLE ...
1
vote
1answer
52 views

Adding users to a list and sort by category?

I'm using SQL-Server 2012 and I would like to know how to solve this program. So: I want to create a page that allows admins to manage a project, and part of it, would be to add members/employees to ...
8
votes
3answers
36k views

How to Handle TimeZone Properly in SQL SERVER?

My local development server is in the Middle East, but my production server is in the UK. I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I ...
1
vote
1answer
587 views

good practices of creating tables in mysql [closed]

Hi I was thinking of developing an web apllication using php and mysql. Application will give a professor the required data about the student. i want to design application considering Engineering ...
-1
votes
1answer
112 views

Research studies on electronic vs paper record keeping accuracy [closed]

Does anyone know of any research studies to test whether electronic or paper record keeping is more accurate than the other? Obviously electronic record keeping is more efficient, but efficient ...
-1
votes
1answer
102 views

Database theories possible topics to research through [closed]

I need to conduct a research on database theories. I want to know what possible questions should I research on which is believed to be suitable when researching database topics for a online assessment ...
1
vote
2answers
148 views

Handling expiration in a course registration or ticketing system

I want to restructure the database for a web application I inherited for handling summer camp registrations, but my question is relevant to any event registration or ticketing system. My question is ...
2
votes
0answers
241 views

How to execute a non-table-locking update operation on PostgreSQL? [closed]

Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
7
votes
3answers
1k views

Foreign keys - link using surrogate or natural key?

Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
2
votes
1answer
408 views

Foreign Keys with Composite Key

Imagine I have a table with the following dependency: (Name, BoughtFrom, TimeBought) --> Cost Where the three attributes in bold form a composite primary key for the table. Now then, I want to ...
6
votes
2answers
2k views

Bitmask Flags with Lookup Tables Clarification

I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
1
vote
0answers
127 views

Real Time data collection - Help select Database

My Scenario: I want to collect data from various sensors[for now I have 600 sensors sending data, but this may have to scale up to 3000 more data sensors, all sensors send real time data] Now I have ...
0
votes
1answer
203 views

List partitioning or filtering a non-partitioned table, which incurs lower cost?

Consider a list partitioned table PARTITIONED__SOURCE_TBL on column period, and non-partitioned table SOURCE_TBL also having the same columns. Assume all tables are adequately indexed. Set 1: ...
1
vote
2answers
886 views

How do I design a database for a resource scheduling and allocation application?

I have two entities: Resource and Project. A Project runs for a certain amount of time (in calendar days), and I need to allocate several Resources to it (allocation interval - 1 work day, not an ...
0
votes
2answers
159 views

Database design - do I need another table?

I am trying to make a database that follows a form that the company uses. When a client walks in the membes of staff have to fill in a form and the data is recorded. The form on paper is 10 pages ...
1
vote
1answer
190 views

Best way to set up database tables

I have 1 user table and want to associate it with multiple other tables depends on if user have added such information (modules) and want to know what is the best way to do it. main table ...
0
votes
0answers
114 views

Database Schema/Structure advice

Well i am reconstructing an already created application and was looking to improve this, especially with in the database structure. I want to start off with probably the biggest section and that is ...
5
votes
1answer
486 views

How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
1
vote
1answer
534 views

Record with variable number of columns?

I am trying to model a cable mapping for devices. Each device has multiple cards, and each of those cards has multiple ports. Since both the number of cards and ports vary, I am confused on how to ...
2
votes
3answers
510 views

How can I improve my table design for different types of an entity?

Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables ...
1
vote
2answers
330 views

Foreign Key constraint on fixed value field - Ever appropriate?

I have a short (15 rows) look-up table that lists the valid values for several columns in the database. I don't think it can be considered a One-True-Lookup-Table, it is about a single definite ...
3
votes
0answers
160 views

Replicating databases for local and cloud instances

Forgive me if this is an inappropriate place for this architectural question but I'm not sure where it best fits in the StackExchange family. I have been tasked with developing an online/live auction ...
8
votes
2answers
3k views

SQL Server database design for “archived but available” data

We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice. ...
2
votes
2answers
300 views

Expanding parent-child tree with additional tables

I have readonly access to a table (Oracle database) which contains a hierarchical structure, represented via parent-child. I need to add another table, which will expand the existing hierarchical ...
1
vote
0answers
667 views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
2
votes
0answers
123 views

Two different ON DELETE paths, how do I need to change the schema?

For a project in school I'm designing a website that sells items via auctions. Think eBay, but on the complexity scale of school project. We went through the process of making an ER Diagram and ...
0
votes
2answers
186 views

Please Review my Schema

Anything obvious that could be improved upon? :) I am positive there are no direct errors. More what I am looking for is errors in normalization and areas that I could simplify data storage. ...
2
votes
1answer
1k views

Modeling a ticket system based on a set of disparate kinds of tickets?

I'm working on a project that allows for the creation of "support tickets." These are not confined to something that needs fixing, however, so in a way they could more accurately be called "jobs." ...
15
votes
4answers
2k views

effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

I am looking for advice on table/index design for the following situation: I have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
1
vote
2answers
302 views

Refactoring/normalization - but almost empty table

I normalized a legacy DB into this structure: But I'm not sure if it is correctly normalized. I don't feel very comfortable with the almost empty filters table. The requirements A group contains ...
3
votes
1answer
76 views

Does avg_query for PgBouncer include update and inserts?

Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage: avg_query Average query duration in ...
-1
votes
1answer
192 views

What is the real-world purpose of an optional 1:1 relationship?

I know that one-to-one relationships can be used to split data into multiple tables for performance or security and that it is used to create a is-a-relationsship. But aside from that things, what is ...
0
votes
0answers
180 views

Ternary Relations three attributes and an association

I have 4 tables: Workers Work Workers Relationship/Work Registration Time I need to have organized the Workers Relationship with Work so you can make a ListBox organized. Where each worker may ...
4
votes
2answers
1k views

Will table inheritance in PostgreSql suit this situation?

Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL. So my main aim is to have a Main schema in this DB, which I am going to use to ...
1
vote
1answer
519 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
0answers
103 views

Unable to decompose this relation to BCNF

I have a relation R = { A, B, C, D, E, F, G, H, I } And functional dependencies F ={ ABC -> DE E -> C AB -> F C -> G F -> H H -> IJ F -> B } I am able to do simple BCNF ...
27
votes
6answers
20k views

In what data type should I store an email address in database?

I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent. For example: this SQL Server recommendation ...