database design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

database design

Author

Topic

keen1
Starting Member

21 Posts

Posted - 2003-08-12 : 01:50:27

i need to design a database for a Production DownTime recording and i have the following requirement:

I have downtime codes and their definitions in one table and also i need to create a 'root causes' and equipments tables for these downtimes. Each downtime definition may have zero or any number of root causes and equipments ( equipments are there to define which downtime is applicable to which equipment! ).

How can i create an efficient table structure and relationships between these tables?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-12 : 03:13:38

Doesn't sound really complicated, unless you have complex business rules hidden away somewhere, or point-haired management

Here's my version (this is pseudo-DDL!):


Create Table Downtime_Master
(
  DowntimeCode VARCHAR(5) NOT NULL PRIMARY KEY,
  DownTimeDesc VARCHAR(50) NOT NULL,
  ...other fields...
)

Create Table Rootcause
(
  RootcauseCode VARCHAR(5) NOT NULL PRIMARY KEY,
  RootcauseDesc VARCHAR(50) NOT NULL,
  ...other fields...
)

Create Table Equipment
(
  EquipmentCode VARCHAR(5) NOT NULL PRIMARY KEY,
  EquipmentDesc VARCHAR(50) NOT NULL,
  ...other fields...
)

--So far all the basic stuff

Create Table Downtime_Rootcause
(
  DowntimeCode VARCHAR(5) NOT NULL PRIMARY KEY, 
  RootcauseCode VARCHAR(5) NOT NULL PRIMARY KEY,
  ...other fields...
  --Downtime and Rootcause codes form the composite primary key here
)

Create Table Downtime_Equipment
(
  DowntimeCode VARCHAR(5) NOT NULL PRIMARY KEY, 
  EquipmentCode VARCHAR(5) NOT NULL PRIMARY KEY,
  ...other fields...
  --Downtime and Equipment codes form the composite primary key here
)

How does it look?

Owais

keen1
Starting Member

21 Posts

Posted - 2003-08-12 : 10:56:56

i forgot to mention a detail ( as always, there are missing points :) ).
One downtime can have different sets of root causes for a specific production line;.
Also each downtime may be assigned to different sets of equipments for different production lines.

how does this fit into your solution? Would you add an extra primary keys to the Rootcause table and the Equipment table like:

Create Table Downtime_Master
(
DowntimeCode VARCHAR(5) NOT NULL PRIMARY KEY,
DownTimeDesc VARCHAR(50) NOT NULL,
...other fields...
)

Create Table Rootcause
(
RootcauseCode VARCHAR(5) NOT NULL PRIMARY KEY,
RootcauseDesc VARCHAR(50) NOT NULL,
ProdLineNo VARCHAR(5) NOT NULL PRIMARY KEY,
...other fields...
)

Create Table Equipment
(
EquipmentCode VARCHAR(5) NOT NULL PRIMARY KEY,
EquipmentDesc VARCHAR(50) NOT NULL,
ProdLineNo VARCHAR(5) NOT NULL PRIMARY KEY,
...other fields...
)

--So far all the basic stuff

Create Table Downtime_Rootcause
(
DowntimeCode VARCHAR(5) NOT NULL PRIMARY KEY,
RootcauseCode VARCHAR(5) NOT NULL PRIMARY KEY,
ProdLineNo VARCHAR(5) NOT NULL PRIMARY KEY,
...other fields...
--Downtime and Rootcause codes and ProdLineNo form the
--composite primary key here
)

Create Table Downtime_Equipment
(
DowntimeCode VARCHAR(5) NOT NULL PRIMARY KEY,
EquipmentCode VARCHAR(5) NOT NULL PRIMARY KEY,
ProdLineNo VARCHAR(5) NOT NULL PRIMARY KEY,
...other fields...
--Downtime and Equipment codes and ProdLineNo form the
--composite primary key here
)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-12 : 13:27:21

Ok, now this is getting slightly tricky. Pause for a moment, step back and take another look at it.

Now tell me, will each production line have its own (different) list of root causes? Or is there a more-or-less static list of root causes, which can apply to any given production line?

If you have scenario 1, then what you have done for the Root Cause table seems to be fine, except I don't think its necessary to make the ProductionLineNo part of the key. Since each root cause is different, you'll want to ensure that the RootCauseCode is different for each. And then you dont need to add ProductionLineNo to the Downtime_Rootcause table either.

However, if the second scenario applies then you need to have two tables, one that contains only the root causes, while another that shows which root causes apply to which production lines. Something like:


--Original table
Create Table Rootcause
(
  RootcauseCode VARCHAR(5) NOT NULL PRIMARY KEY,
  RootcauseDesc VARCHAR(50) NOT NULL,
  ...other fields...
)

Create Table ProductionLine_Rootcause
(
RootcauseCode VARCHAR(5) NOT NULL PRIMARY KEY,
ProdLineNo VARCHAR(5) NOT NULL PRIMARY KEY,
...other fields...
)

In this case, the Downtime_Rootcause table structure you propose should be fine.

Owais

keen1
Starting Member

21 Posts

Posted - 2003-08-13 : 01:24:29

Hello Owais,

scenario1 applies to this application.

In fact, let me tell you why there is a ProductionLineNo in the Root Cause table. When i first started working on this application 4 years ago, i was a real newbie to databases so i have made some 'nice' decisions :) And i have created seperate Root Cause tables for each production line! Each production line has a different set of root causes so this was the decision but after getting used to databases, now it is obvious for me that it was a 'bad' decision.
In this original design, i each Root Cause tables for the lines, there is an identity column which gets is incremented automatically by SQLServer7.0 when a new Root Cause is added and that is the RootCauseCode :)
So, it seems to me that the existing Root Cause codes needs to be changed and the new table that you mention contains unique Root Cause codes for all the factory!
For this, i will need to ask the users not to define any new Root Causes for several days and after that i need to convert all the downtime records to reflect the new Root Cause codes! So you see, there are around three years of data need to be converted.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-13 : 03:11:41

You are going to prevent users from doing something for several days??!! And you think they'll listen?

I dont think your migration will take a lot of work. Easiest way is to create a new table with a structure like this:


Create Table RootCause_New
(
  RootcauseCode INT,
  RootcauseDesc VARCHAR(50),
  ProductionLineNo SMALLINT
  OldRootcauseCode INT
)

You could make RootcauseCode an IDENTITY column, but try would avoid IDENTITY columns as much as you can, look for natural key candidates. You could also generate a unique new code by concatenating or merging the ProductionLineNo and the existing RootcauseCode.

Now to fill this new table you can do a UNION of all the existing Rootcause tables:


INSERT INTO Rootcause_New (RootcauseDesc, ProductionLineNo, OldRootCauseCode)

SELECT RootcauseDesc, 1, RootcauseCode FROM RootCausesForLine1
UNION ALL
SELECT RootcauseDesc, 2, RootcauseCode FROM RootCausesForLine2
UNION ALL
SELECT RootcauseDesc, 3, RootcauseCode FROM RootCausesForLine3
....

Now you have all the old data in a single table, and to change the data in any table that reference the old table with the new RootcauseCode's you can simply do an UPDATE with an INNER JOIN:


UPDATE d
SET d.NewRootcauseCode = r.RootcauseCode
FROM Downtime_RootCause d 
INNER JOIN Rootcause_New r
ON d.OldRootcauseCode = r.OldRootcauseCode
AND d.ProductionLineNo = r.ProductionLineNo

After you are done with this you can remove any redundant columns and rename the tables appropriately. Put all of this code (after testing it on a development server of course) into a single script file, and hit RUN!!

Oh, and dont forget to take a back up of your data before you do something like this.

Owais

keen1
Starting Member

21 Posts

Posted - 2003-08-13 : 07:00:54

Owais,
is it possible to define two primary keys for a table where the combination of these columns has unique values but individual columns do not?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-13 : 07:44:06

Two primary keys? Hell, no

! I think you mean't a composite primary key, a primary key that comprises of more than one column, yes that's possible, and more common than you think. Just like you mentioned it looks at a unique combination of the comprising columns to enforce referential integrity. Look it up in the Books Online.

Owais

keen1
Starting Member

21 Posts

Posted - 2003-08-13 : 08:47:30

i will analyze the structure again and i will try to create the queries for selecting and updating the master downtime records table.
At the end, those query performances will define the overall performance of the system.
I will post my tries and may be you can have a look at it.

Thank you Owais

keen1
Starting Member

21 Posts

Posted - 2003-08-14 : 03:35:26

Owais,

in your design, table RootCause contains the root causes and the table DownTime_RootCause contains which root causes are applicable to which downtime code.
is it a better design to delete the DownTime_RootCause table, add a downtime code to the RootCause table and make the downtime code and root cause code as composite primary keys?
What is the advantage of using your original idea?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-14 : 04:02:39

I think I am a little confused about whether we are discussing base data tables here, or transactional tables. For example, the Downtime table, does it contain a list of downtime types, or actual downtime occurrances? I was thinking actual downtimes, but now I think that we have only discussing base tables so far.

Ok going with that base table assumption, you need to decide what is the relationship between Downtime and Rootcause. Is it one-to-many, many-to-one, or many-to-many? Think actual scenarios, does one rootcause apply only to one downtime, while a single downtime can have multiple rootcauses? In that case you could very well include DowntimeCode in the Rootcause Table like you suggest, since Downtime could be described as an attribute of Rootcause.

However if your requirements suggest that the same Rootcause could apply to more than one downtime, and one downtime could have more than one Rootcause, you will need the Downtime_Rootcause to enumerate the valid combinations then.

I think you need to take a high-level view of your database structure, I suspect you have a couple of tables that could be done away with if they come around to being one-to-one relationships.

Owais

keen1
Starting Member

21 Posts

Posted - 2003-08-14 : 06:31:58

:) ok it is my problem of explanation.

of course there is a master table which is pre-filled automatically by the downtime records ( including start and end times of the downtimes and the duration ) then the user is required to fill in the downtime code column, root cause and the equipment column.

The user views the records of downtime by selecting the production line and the time range.

When the user selects from the applicable downtime codes for that line, the root cause and the equipment columns will show the applicable codes respectively in different dropdown boxes.

Up to now, we generally talked about the base tables but of course there is the actual downtimes selection operation by the user.

The relationship between Downtime and Rootcause is one-to-many. But in the actual case, user is only able to select one root cause for a specific downtime record.

Onder

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-14 : 09:30:26

quote:
The relationship between Downtime and Rootcause is one-to-many

Ok, so this means each rootcause is effectively unique, and applies only to one downtime. Which means you can chuck the downtime_rootcause table, and add a DowntimeCode column in the Rootcause table (like you suggested). And again when you create the actual downtime occurrances table, you'll only need one column to record the RootCauseCode.

So, now your structure should contain only the following tables:

1. ProductionLines (ProductionLineNo, Description, blah)
2. Equipment (EquipmentCode, ProductionLineNo, Description)
3. Downtimes (DowntimeCode, EquipmentCode, Description)
4. Rootcauses (RootcauseCode, DowntimeCode, Description)
5. ActualDowntime (Downtime_BeginDatetime, Downtime_Endtime, RootcauseCode, blah..blah)

The last one is just a guess...

Owais

keen1
Starting Member

21 Posts

Posted - 2003-08-15 : 01:28:52

Good morning Owais,
sorry that i have misguided you in my last post! In fact, like i have written in one of my previous post, each downtime code has a different set of root causes for each production line! I know, practically this does not mean 'The relationship between Downtime and Rootcause is one-to-many'. In fact, 'The relationship between the 'Downtime&ProdLine combination' and Rootcause is one-to-many'

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-15 : 06:38:06

I think that's ok, since we've already related downtimes to equipment and equipment to production lines. Effectively, each production line has its list of equipment, each piece of equipment has its own downtimes. Then each downtime has its own root causes.

Sounds ok?

Owais

keen1
Starting Member

21 Posts

Posted - 2003-09-02 : 15:09:29

Hello again,

in my DTRecords main table, DTStartTime and DTEndTime are the primary keys but there is another issue here.
The DTAttr column is the column for keeping track of the manipulations done on the original record or a new record. In the existing system attributes are like:
0 : automatically created record
1 : manually divided record
2 : manually added record
3 : deleted record

As you see, we never delete a downtime record! We only change it's attribute so we don't loose them and also we never take into account these records in the reports.
But since there can be two records with the same start/end dates but with different attributes, i need to add the attribute column to the composite primary key!

Note : Nearly all of the queries for reporting use the start/end times and the attribute column.

Does it make sense to use three columns for the composite primary key? Does it affect the performance?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-03 : 02:55:29

I think it's perfectly okay to add the attribute column to the primary key. Remember, a primary key must identify every row uniquely, so if a combination of three columns represents the identity of a row, those three columns need to form the composite primary key of the table. It's unlikely to have a major impact on performance, particularly considering you have "narrow" columns in the key, as opposed to "wide" datatypes such as char and varchar. Since almost all queries use these three columns in the WHERE clause, I think you will benefit from creating a Clustered Primary Key Index on the column.

Owais

Make it idiot proof and someone will make a better idiot

keen1
Starting Member

21 Posts

Posted - 2003-09-03 : 03:18:56

Thank you Owais,

i do not think that it is very likely to have a downtime record for two different production lines having the same start/end times and attribute; but for information, how does the performance change with the number of columns making the composite primary key? For the above example, it makes 4 columns!

Another question. Can i have a table with no primary keys ( in this case, it is the DTEquipments table )? Or should i put an identity column just to include a primary key? For info, i could not find a good primary key for that table because, in our case, there are equipments that have the same equipment code for a specific prod. line!!! It is not a good convention but i can not change the coding system for that production line!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-03 : 05:22:47

Like I said, add as many columns as needed to have the right primary key. If your primary key needs to be 5 columns wide, so be it. And you dont have much to worry about, since you will still have a narrow primary key. But it would be worthwile to take another look at the data, can you really have two records for a production line downtime with the same StartTime and EndTime, but with different attributes?

About the other question, tell, how can you differentiate one equipment from another if they have the same equipment codes. Like you mention, if you have two eqpt with the same codes in the same production line, and you wanted to update the attributes of one of them, how would you write an UPDATE statement? If your requirements are genuinely such, then I think you will need to add a surrogate Primary Key (INDENTITY). But then you will also have to add extra validations to the system to prevent users from adding more equipment in the system than actually exist. Brace yourself for data problems, then!

Owais

Make it idiot proof and someone will make a better idiot

keen1
Starting Member

21 Posts

Posted - 2003-09-03 : 06:55:42

For the first part, it will most probably not happen; so i will not include the attribute column to the composite key. But, start/end times and the production line will exist in the key.

For the second part, i completely agree with you but that convention is the convention used by the machine manufacturer and the prod. line managers! So i can not present them with a different convention.
Equipment table is very static; users do not insert/update any record of that table. i, myself as the database admin, insert all the codes whenever a production line is commissioned or a new part is added to the line! And that happens very rarely in the long run!
So i think it can be a keyless table!

keen1
Starting Member

21 Posts

Posted - 2003-09-08 : 04:27:33

Hi Owais,

we have quite a discussion with my collegues from the IT dept. about the composite key; i want to go on with the composite key approach like you said.

If i choose three columns as the composite key, how should i do the indexing? As far as i know, if there is no index specified explicitly by the user, then SQLServer automatically selects the primary key as the index. How does this work with the composite key?

Also, would i write the 'update' statements with the composite keys in the where clause?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources