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.
2
votes
0answers
59 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 ...
4
votes
2answers
95 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 ...
0
votes
1answer
66 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 ...
2
votes
1answer
140 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
54 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
52 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
121 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
84 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
37 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
44 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 ...
4
votes
1answer
69 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
83 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
2answers
100 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
65 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 ...
2
votes
0answers
38 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 ...
4
votes
1answer
154 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
1answer
64 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 ...
0
votes
0answers
71 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
47 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
80 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
112 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."
...
5
votes
1answer
221 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
218 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
0answers
23 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
77 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
46 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 ...
0
votes
0answers
47 views
What are examples of optional one-to-one relations? [closed]
I can imagine relations where one or no site is optional, but not when both are optional.
What are examples for one-to-one relations where both ends are optional?
The relation I'm referring to looks ...
0
votes
0answers
37 views
MySQL Stored Procedure
I am developing a database for an eCommerce website, I have written the stored procedure for the following tables,
user_master,
authentication_master,
country_master,
public_master.
I have ...
2
votes
1answer
102 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
85 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
53 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 ...
20
votes
6answers
1k 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 ...
1
vote
1answer
59 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
86 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
72 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
151 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 ...
1
vote
3answers
148 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 ...
6
votes
3answers
114 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 ...
0
votes
0answers
37 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
54 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
78 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
166 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
115 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
95 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
0answers
71 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, ...
4
votes
1answer
124 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 ...
4
votes
2answers
86 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
72 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
71 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
85 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 ...
