Critique My DB Design

Question

I don't have much experience designing DB. Though I have some rough theoretical knowledge.

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.

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.

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.

Any of these entities can be renamed/readdressed.

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.

Location             TU
|                    |
+- Device            +- Card 
    |                    |
    +-- Alarm            +--Contact 
        |                     |
        +----Alarm-Contact----+

My Design: 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.

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.

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.

Thanks

Answer 1

If you just create an Alarm-Contact-History table you will not be creating an accurate history. Assuming each card can possibly be moved to another TU, the history you described would not know what TU the Card was installed in when the Alarm was using the Card's Contact(s).

Maybe an enhanced Alarm-Contact-History that includes the Card Id, TU Id, Location Id, and Device Id would be better. That way you have a picture of the exact configuration at the time the history record was generated. If something moves around in the intervening time frame you won't mind because you knew how it was at that point in time.

SQL Server will continue to increment the values assigned to the identifier. How many records do you anticipate having? If it is a large amount (~ gt 2.14 billion) I would recommend using a bigint for the identifier.

Answer 2

I suggest a different plan.

As a warm-up convert your data from Excel to MS Access. The conversion is very easy, because Excel and Access interoperate.

Do not use auto-increment numbers. Instead, arrange for a function that you write in VBA or whatever to run whenever a new number is to be inserted. Create your own algorithm for controlling the assignment of a new number, instead of being at the mercy of the DBMS.

Use the interactive query builders and such in MS Access either to help you gain practice or to save you effort. But use SQL view to figure out how all the queries work, even if you didn't build them.

Then build a SQL Server version. Abstract out the features of MS Access that are peculiar to its dialect of SQL, but use most of what you have learned building the prototype. Your auto-increment code will be in a trigger.

Oh, and by the way, don't forget the theory you have learned. It may come in handy.