Databases
StackOverflow2013
Postsdbo
POCritique My DB Design
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCritique My DB Design
    text
    copied!<p>I don't have much experience designing DB. Though I have some rough theoretical knowledge.</p> <p>So, on to my problem. We have data on a bunch of excel files (yah, surprise!) and we want to move them to a DB.</p> <p>To simplify, let's say the system is a centralized alarm system. Data get collected from remote locations and displayed in a centralized monitoring room. Each location have a unique name and multiple devices. Within a location, device name is unique and the device have multiple alarms. And within a Device, an alarm name is unique.</p> <p>In each location we have one or more Terminal Unit (TU) that aggregates the alarms in that location and send it. Each TU have a bunch of cards within it, each with unique id's per TU. Each card have a wiring termination contact for each alarm with a unique address per card.</p> <p>Any of these entities can be renamed/readdressed.</p> <p>As you see the data is highly hierarchical. And I need to store the history of each wiring contact-alarm assignment (relationship). An alarm can change it's assignment from one contact to another.</p> <pre><code>Location TU | | +- Device +- Card | | +-- Alarm +--Contact | | +----Alarm-Contact----+ </code></pre> <p><em>My Design:</em> I created a table for each entity shown above. I used artificial primary keys for all of them as auto-increment integers. A table down in the hierarchy (Tn) will have a uniqueness constraint on the composition (Tn.name, Tn-1.pk, Tn-2.pk,...), where Tn is a table (n)th depth in the hierarchy and (pk) is the primary key of a table.</p> <p>I will be using SQL Server and I'm having doubts with the auto-increment integers PK. Let's say I have 10 records and I deleted all but the last (10th) . Will the next added record be numbered 11, or will the DBMS renumber them as 10->1 and new -> 2. If the former is correct, how to solve the problem of PK overflow.</p> <p>Another question is how should I model historical data for this. I'm thinking I should create Alarm-Contact-History table with n-1 relationship to Alarm-Contact table.</p> <p>Thanks</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload