Questions tagged [physical-design]
The physical-design tag has no summary.
40 questions
0 votes
1 answer
69 views
What happens if I insert a row in the middle of an sqlite table `WITHOUT ROWID`?
WITHOUT ROWID is an sqlite optimization. Doc states, it is making the primary key of the table to a clustered index. As far I know, clustered index means that the physical ordering of the rows will ...
- 2,137
1 vote
0 answers
41 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 ...
- 378
-1 votes
1 answer
181 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 ...
- 117
0 votes
1 answer
236 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
132 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
91 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
1k 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
255 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
1k 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 ...
- 119
-1 votes
1 answer
163 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 ...
- 99
5 votes
1 answer
1k 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 ...
- 65
8 votes
2 answers
2k 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
2 votes
1 answer
185 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 ...
- 21
7 votes
1 answer
7k 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 ...
- 193
1 vote
3 answers
500 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