Questions tagged [physical-design]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
33 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 [...]&...
user avatar
  • 33
-3 votes
1 answer
63 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 ...
user avatar
  • 3
5 votes
3 answers
226 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 ...
user avatar
0 votes
1 answer
53 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 ...
user avatar
0 votes
3 answers
384 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 ...
user avatar
  • 91
0 votes
1 answer
57 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 ...
user avatar
  • 101
4 votes
1 answer
450 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 ...
user avatar
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 ...
user avatar
1 vote
1 answer
75 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 ...
user avatar
  • 11
6 votes
1 answer
4k 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 ...
user avatar
  • 183
1 vote
3 answers
214 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 ...
user avatar
2 votes
1 answer
449 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 ...
user avatar
  • 209
2 votes
1 answer
157 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 ...
user avatar
  • 209
3 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 ...
user avatar
  • 232
3 votes
2 answers
1k 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?
user avatar
  • 165
5 votes
1 answer
230 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, ...
user avatar
2 votes
2 answers
410 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 ...
user avatar
-1 votes
1 answer
941 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!
user avatar
  • 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; ...
user avatar
  • 21.3k
0 votes
1 answer
42 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, ...
user avatar
  • 1,493
3 votes
1 answer
224 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 ...
user avatar
  • 133
3 votes
1 answer
408 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 ...
user avatar
  • 133
5 votes
2 answers
66 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+ ...
user avatar
6 votes
2 answers
932 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 ) ...
user avatar
23 votes
1 answer
6k 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 ...
user avatar
  • 331
3 votes
1 answer
79 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 ...
user avatar
1 vote
1 answer
385 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 ...
user avatar
  • 11
1 vote
2 answers
353 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 ...
user avatar
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 ...
user avatar
14 votes
3 answers
42k 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 ...
user avatar
  • 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 ...
user avatar
  • 2,323
9 votes
1 answer
3k 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 ...
user avatar
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 ...
user avatar
  • 823
3 votes
1 answer
5k 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 ...
user avatar
  • 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 ...
user avatar
  • 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....
user avatar