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
436 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
138 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
124 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
87 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
330 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
290 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
368 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
203 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
101 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
788 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
205 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
447 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
102 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
140 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. ...
1
vote
1answer
575 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." ...
13
votes
3answers
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
286 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
62 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
128 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
147 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
1answer
593 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
322 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
83 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 ...
22
votes
6answers
10k 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 ...
2
votes
1answer
440 views

Best practice around sys.dm_db_missing_index_group_stats in SQL Server

I have a fairly simple query using sys.dm_db_missing_index_group_stats that identifies missing indexes in my SQL Server database. This is pretty commonly used. My question is how do I identify which ...
6
votes
1answer
149 views

What is a good way to determine the performance I should expect from a PostgreSQL server?

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
1
vote
1answer
105 views

big db for managing user files and settings

Hi im about to start a project that will hopefully eventually hit milion+ users which is some kind of a site managment, problem is how to set the db in mysql, some people suggest way 1 and others way ...
7
votes
2answers
393 views

Best relational database structure for this data

I'm in the process of creating a database scheme for the following scenario: There are users Users have roles (such as "Developer" or "CEO") Roles have applications (such as "Topdesk") Applications ...
2
votes
3answers
586 views

Database Design Relationship for Patient Visit in MS Access

I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have. There are a total of 6 ...
7
votes
4answers
220 views

Arbitrarily ordering records in a table

A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
1
vote
1answer
153 views

NoSQL approach: design table “scheme”

We need to store simple log data in a database, but we also need to get and filter data in realtime, based on 'user', simple 'tag' (ie. redis, memcache, php, mysql, etc.) and timestamp. We need to ...
0
votes
2answers
90 views

Adding a bool column; tracking untouched vs. false

[backstory] I have a simple database of our current Widget inventory. It averages just dozen columns across five or six tables, but has a huge number of records already. Some Widgets came with ...
1
vote
2answers
140 views

Database design that handles data growth with time

I have a big table that stores video rental data with the following columns: id, title, language, duration, owner, remarks, closing_date_for_loan Assuming every day there are thousands of data ...
3
votes
4answers
221 views

PRIMARY KEY CONSTRAINT fundamentals

Can you explain why "primary key constraint" exists if, as you know, it is possible to create relationships between tables using unique indexes only ? The RDBMS that is used is SQL Server. In fact, ...
0
votes
3answers
383 views

Rent weekly cost database design

I have a database which contains a table BUILDING with in each row details about some building, another table BUILDING_UNIT contains rows with details about a single building unit which refers with a ...
1
vote
2answers
238 views

Database Design question, calculate or save financial positions

As the question states i'm currently internally strugling witha database design question. I work as a software developer (and i tend to do everything database related aswell since we're rather small) ...
4
votes
1answer
489 views

Does PgBouncer create a separate pool for every database config entry?

If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one? I would think so, but have not been able to confirm this. For example, ...
5
votes
1answer
406 views

Enforce data integrity with a recursive table

I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
5
votes
2answers
439 views

How to properly configure PostgreSQL RAM usage?

Using PostgreSQL, we have several hundred concurrent processes doing regular database access. We have our data spread out through several database servers, each of which houses a connection pool with ...
2
votes
0answers
309 views

How to properly configure pgBouncer transaction level pooling?

What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
1
vote
1answer
184 views

Database Relational Design for Multiple Categories

I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants ...
1
vote
1answer
150 views

Approach to Analyzing Database Requirements

When presented with requirements for a database driven system, I always find it useful to imagine a world with no computers, where data records were kept in filing cabinets and data was collected by ...
2
votes
0answers
102 views

Digital asset management

I'm working in a company that currently develops Media Asset Management system and now we want to improve our system for document files. I have to search nearly thousand .doc and .pdf files and index ...
1
vote
1answer
109 views

Public Database for Mobile Application

What are the proper steps to hosting a public database. Currently I a building a mobile application with Adobe Flash Builder. I have used Microsoft SQL Server, My SQL as well as WAMP Server. All of ...
2
votes
1answer
261 views

IP Address lookup star schema design

I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets and now the next steps is to populate this datasets in to the table. And then have some ...
1
vote
2answers
245 views

Use single table with foreign key or two twin tables for text entries?

I have a table answers that has several columns including the content iself such as ID | q_id | list_id | user_module_id | content | updated_timestamp The content column is a varchar since we want ...
1
vote
2answers
201 views

Extensible Asset Database Schema

Background I work for a decent sized company in the hardware sector. Currently, we have a number of varying assets that need to be tracked. Until recently, this was done by hand as was an ...
2
votes
2answers
1k views

Database design for an E-commerce website

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand ...
3
votes
3answers
4k views

How to model inheritance of two tables mysql

I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal). ...
2
votes
3answers
742 views

Simple scenario needs to go from Unnormalized Data to 3NF - Review Please

NOTE: The Question has been updated. View Edit Revisions to view original question. The scenario. An animal charity that at the moment, track their establishment's costs via a spreadsheet like so: ...