Questions tagged [physical-design]
The physical-design tag has no usage guidance.
39
questions
0
votes
0
answers
12
views
Data Modeling Problem - Schema to store approval process
I need to design a schema to keep track of data changes (I do not need Change Tracking or Change Data Capture or Temporal Tables) to columns in a few tables. When the data is created, modified, or ...
- 368
0
votes
1
answer
65
views
What data "MUST" be stored inside a "relational database"?
I am working on a mobile/web app like Instagram. Considering that, I want to know for what part of the app it's better to use relational and for what other parts non-relational databases.
I have ...
- 119
0
votes
1
answer
76
views
Design options: multi clusters, multi instances or multi availability groups
We have a Microsoft platform, where within it, we have two sets of databases. One set of databases for the platform services etc. and one set of databases of more custom nature with some data that is ...
0
votes
1
answer
88
views
Primary File Organization in DBMS - Files of ordered records (sorted files)
"Fundamental of Database Systems", 3rd ed. by Elmasri and Navathe, page 136 says:
"We can physically order the records of a file on disk based on the values of one of their fields [...]&...
- 33
-3
votes
1
answer
79
views
Striping disks with MAXDOP 1
We're working with a vendor who is insisting we use MAXDOP = 1 (I know...). They are also emphasizing the importance of striping our data files across different disks.
As I understand it, an I/O ...
- 3
6
votes
3
answers
940
views
Enforce uniqueness across a one-to-many-to-many bridged relationship when constraining to one-to-many
We have defined a series of configurations, where, driven by a RESTful API, end-users can build up new revisions. Some of the components of the configuration can have more than one value; a revision ...
- 175
0
votes
1
answer
153
views
B2B application: users has its own users
B2B Scenario I am trying is that:
The following Roles exists in the system:
Super Admin
Super Admin Staff
Owner (client)
Admin (owner i.e. client and creates admin to define sales counters)
Sales ...
- 5
0
votes
3
answers
837
views
char or smallint for a column with numeric codes?
I have columns in my application that have values that always have 4 digits, e.g. '0100' or '1230'. One of examples could be Merchant Category Code.
For the time being, I have set the type of this ...
- 111
0
votes
1
answer
96
views
Db2 LUW tens of millions of rows
We have a table with tens of millions of time series rows. We index by three foreign keys to relations. Let's hypothetically say medical observation row to doctor, acl, and person. Main thing being a ...
- 101
4
votes
1
answer
862
views
PostgreSQL Index Only Scan can NOT return ctid
I have this table in PostgreSQL 11:
CREATE TABLE A (id bigint PRIMARY KEY, text text)
Now I want to fetch the ctid of rows meeting some condition like id = 123.
However, even using pg_hint_plan to ...
- 55
8
votes
2
answers
1k
views
Fastest way to split/store a long string for charindex function
I have a 1 TB string of digits. Given a 12-character sequence of digits I want to get the start-position of this sequence in the original string (charindex function).
I have tested this with a 1GB ...
- 181
1
vote
1
answer
137
views
MSSQL database physical design: RAID or spreading filegroups across different physical drives?
I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size. There are some quite large tables in the DB with more about 1010 .. 11 records. Every table has ...
- 11
6
votes
1
answer
6k
views
One big table vs few smaller
Please take note that the example below is just an example, my scenario is way more complex and the way i'm trying to model it really makes sense
Let's say i'm creating a table for audit events in ...
- 183
1
vote
3
answers
318
views
Clustered Primary Key that is never used vs. Non-Clustered Primary Key on Multiple Columns
I am working on a table design for Customer Totals and trying to make a decision about the primary key. I was going to go with a surrogate identity column with a clustered index, but this column would ...
- 141
2
votes
1
answer
629
views
How to Store Skip Lists on Disk
I am new to thinking in terms of databases. I have read about how B-trees are stored on disk but I haven't seen an actual implementation or read about how it works for B-trees.
Wondering how one ...
- 189
2
votes
1
answer
208
views
How to Store Trees on Disk
I am wondering how to store trees on a physical disk, for perhaps a tree-oriented database like an XML database.
Wikipedia states about XML databases:
...custom optimized data structures are used ...
- 189
4
votes
3
answers
3k
views
Performance of using a non-clustered index as primary key
Background
I am designing a database for a record management system. It's early stages as I am considering what to use as a primary key: INT or UNIQUEIDENTIFIER.
The reason for considering ...
- 252
3
votes
2
answers
2k
views
Most efficient data type for a column storing numeric values between 0 and 100
On my MySQL DB I was thinking of using TINYINT (Unsigned)?
Would you use Byte on SQL Server?
- 165
5
votes
1
answer
274
views
Can you gain the performance benefits of partition elimination without changing SQL module or table definitions?
I work for a company that has a multi-tenant database model that doesn't currently use database partitioning. The ideal end-state would be adding a new TenantID column to every existing table, ...
- 515
2
votes
2
answers
421
views
Best Way To Migrate Existing Production Database To New Physical Storage Structure
I have recently taken a job as a Senior Database Administrator at a small but rapidly growing company. My first order of business was a clean up the logical design of the main database (adding primary ...
- 515
-1
votes
1
answer
1k
views
Logical model in workbench
I have a physical data model of db in MySQL Workbench, how i can create an a logical model of this db(like in Allfusion data modeler(ERwin))? May be
Sorry for my english!
- 1
1
vote
1
answer
2k
views
DBPATH, LOGPATH and STORAGEPATH
Assuming that there are only two available paths, say /log and /data. Would it be better to set DBPATH to /log or /data. I.e.
CREATE DATABASE WHATEVER
AUTOMATIC STORAGE YES
ON /data DBPATH ON /log;
...
- 23.1k
0
votes
1
answer
46
views
Mapping 5 columns to 1 (ID, dimension) table without 5 joins?
I'm using survey monkey's API to dump survey data into a SQL data warehouse.
So I'm dumping response data into a table 'resonses' thusly:
survey_id, Q1, Q2, Q3, Q4, Q5
survey3944, 848948344, ...
- 1,651
3
votes
1
answer
409
views
How does Hash Join works with relational operators other than equality operator?
I have been reading about Hash Join and how does it work in physical level. However, there is some thing that I don't understand (because of my lack of knowledge).
This is the algorithm of Hash Join ...
- 133
3
votes
1
answer
449
views
Should a table be split if only a particular column gets updates?
I have a table in my MySQL 5.7 database, which has 9 columns. One column is an AUTO INCREMENT PRIMARY KEY, where as the other 6 contain some data, which isn't going to be updated in a year. Now, the ...
- 133
5
votes
2
answers
77
views
Allocation strategy where tables regularly grow and shrink by large amounts
I have a SQL Server 2014 database with 100+ tables that have a 5% growth rate over the course of a year. Lots of reading, very little writing.
This database is going to expand by another set of 20+ ...
- 153
6
votes
2
answers
1k
views
Wide clustered index vs multiple narrow nonclustered indexes?
Say I have a contrived Student table like so:
CREATE TABLE Student (
Id IDENTITY INT,
SchoolId INT NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL
)
...
26
votes
1
answer
7k
views
Composite Primary Key in multi-tenant SQL Server database
I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will ...
- 361
3
votes
1
answer
84
views
In SQL Server would misc pages be pushed out when an object grows within a mixed extent?
In SQL Server 2008-2012, objects (including tables) are first put in a mixed extent. An extent can accommodate up to 8 pages and accommodates more than one type of page (meaning: pages from more than ...
- 711
1
vote
1
answer
452
views
ER-Model for Borrowing-Lending-Network?
I plan on creating my first database so this is going to be a newbie question. My database is supposed to be for a Borrowing-Lending-Network where users can borrow products from other users. Creating ...
- 11
1
vote
2
answers
378
views
Index and rows placement within database files
Where are the indexes and data tables stored within a physical database file or a page of a physical file :
Can there be different rows from different tables in the same
physical page ?
How does ...
5
votes
1
answer
4k
views
Does it make sense to run a cluster of database servers in docker containers?
I'm currently looking into setting up a database cluster (MySQL Galera or Percona) but I have only 2 machines.
However, according to many sources I've read (This article from the Mariadb KB), the ...
- 53
14
votes
3
answers
45k
views
What is the fastest way to fetch the last row from a table?
I have a PostgreSQL table, Prices, with the columns:
price (Decimal)
product_id (Int)
There are also created_at and updated_at columns.
Prices get updated regularly and I keep old prices in the ...
- 315
1
vote
1
answer
195
views
Data center from scratch which way to go
My budget for a data center (hardware,OS licences, SQL Licences WithOut CALS ) is around $50K~$60K.
I have needs for around 200 users and my apps works only works in Microsoft environment (Window ...
- 2,403
11
votes
1
answer
4k
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,558
2
votes
3
answers
4k
views
Usefulness of a multi-attribute index
If an index has more than one attribute in it, is there any speed gained in a select statement whose where clause uses one of the attributes in the index?
For example, take a table T with an index on ...
- 863
3
votes
1
answer
6k
views
How to design tables for players in a tournament?
I need help with my database table design.
I have group which contains a list of players. In every group each player plays games with other players. Game has also some result for example 1:0 and ...
- 133
5
votes
2
answers
18k
views
Separate stored procedures for inserts and updates?
I have a table in Microsoft SQL Server. Sometimes I need to update, and sometimes I need to insert. I could write 2 stored procedures:
InsertNewPerson
UpdatePertsonById
But I was thinking to write ...
- 1,223
4
votes
1
answer
2k
views
What is the best practice for physical deployment of data marts?
We are just starting to get into business intelligence at our company. We have some architects who are designing our data warehouse and also our data marts. They are using star schema for both designs....
- 4,906