September 17, 2009 at 10:19 am
Hi Everyone,
Thank you for all your helps and suggestions (Matt Whitfield, Ian Scarlett, Jeff Moden).
Iām humble to learn and make any corrections.
I redesign and still tested. Enclosed is my second design.
-- Create Operators Table
CREATE TABLE Operator (
OperatorID smallint NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
OpsFirstName CHAR(35) NOT NULL,
OpsMiddleName CHAR(2) SPARSE NULL,
OpsLastName CHAR(35) NOT NULL,
CompanyName CHAR(75) SPARSE NULL);
GO
-- Create LockOutTagOut
CREATE TABLE LockOutTagOut (
TicketID VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED,
CallerDate DATE NOT NULL,
CleranceNumberStopTime TIME NOT NULL,
CleranceNumberStartTime TIME NOT NULL,
CleranceNumberNotes NVARCHAR(250) NOT NULL,
AdditionalNotes NVARCHAR(250) SPARSE NULL,
WindFarmStatus char(50) SPARSE NULL,
CrewSize SMALLINT NULL,
OperatorID SMALLINT NOT NULL,
SiteID SMALLINT NOT NULL,
TurbineID VARCHAR(10) NOT NULL,
CallerID SMALLINT NOT NULL);
GO
--Create Site Table
CREATE TABLE Site (
SiteID SMALLINT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
SiteName CHAR (100) NOT NULL);
GO
-- Create Turbine Table
CREATE TABLE Turbine (
TurbineID VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED,
TurbineStatus CHAR(100) NOT NULL);
GO
-- CREATE Caller TABLE
CREATE TABLE Caller (
CallerID SMALLINT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CallerFirstName CHAR(35) NOT NULL,
CallerMiddleName CHAR(2) SPARSE NULL,
CallerLastName CHAR(35) NOT NULL);
GO
ALTER TABLE LockOutTagOut
ADD CONSTRAINT fk_Operator FOREIGN KEY (OperatorID)
REFERENCES Operator (OperatorID);
GO
ALTER TABLE LockOutTagOut
ADD CONSTRAINT fk_Caller FOREIGN KEY (CallerID)
REFERENCES CALLER (CallerID);
GO
ALTER TABLE LockOutTagOut
ADD CONSTRAINT fk_Turbine FOREIGN KEY (TurbineID)
REFERENCES Turbine(TurbineID);
GO
ALTER TABLE LockOutTagOut
ADD CONSTRAINT fk_Site FOREIGN KEY (SiteID)
REFERENCES Site(SiteID);
GO
Questions:
On my LockOutTagOut table, I need to capture ClearanceNumberStopTime and ClearanceNumberStartTime values as
10:00 AM or 5:00 PM. I all ready assigned TIME data type but it did not display a value of 10:00 AM.
What type of data type should I assign to capture 10:00 AM or 5:00 PM? I
Test Data:
TicketID CallerDate CleranceNumberStopTime CleranceNumberStartTime CleranceNumberNotes AdditionalNotes WindFarmStatus CrewSize OperatorID SiteID TurbineID CallerID
---------- ---------- ---------------------- ----------------------- ----------------------------------------------------------------------------------------------- ---------- ------ ---------- --------
1111 2009-09-17 11:00:00.0000000 15:00:00.0000000 Design Modification Tests Additional Notes Offsite 2 2 1 A16 2
1112 2009-09-17 09:00:00.0000000 17:00:00.0000000 Install Work Cage Tests Additional Notes Offsite 2 2 1 A35 3
1113 2009-09-17 11:00:00.0000000 14:00:00.0000000 Failure on Generator Tests Additional Notes Offsite 2 2 1 A16 3
Also, I want to make sure TicketID, SiteID, and TurbineID becomes unique keys.
Did my design capture or meet this requirement?
-Edwin
September 17, 2009 at 11:17 am
Edwin
It did store the time as you wanted, but it's displaying it in 24 hour format. Check out CONVERT in the documentation for more info about converting dates/times to strings with specific formatting.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 17, 2009 at 6:20 pm
Edwin-376531 (9/17/2009)
Hi Jeff,The business rules did not allow two mobile phones in my case.
Thanks for your hints.
-Edwin
Ok and understood. So how about this? It's a bad practice to store phone numbers with names as it violates normalization and will eventually make your code fail when the business rules or needs of the data do change sometime in the future? Would that be a good enough reason? š
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2009 at 6:42 pm
Ian Scarlett (9/17/2009)
Jeff Moden (9/16/2009)
That's the whole idea behind using the surrogate key instead of a natural key which may be duplicated. The users wouldn't see it but the join to and address table or the company name (which should actually be in another table) will provide the users with enough information in the GUI to figure out which Joe Bloggs you're talking about.
But the natural key shouldn't be duplicated either. Witness this very site, where there was no unique constraint on Username, which is the natural key.
If you are saying that the operator's name in conjunction with address and company is the natural key, then that should be protected by a unique constraint. Unless it is, I could add Joe Bloggs at 1 The Street from Acme Co more than once, and you still can't distinguish them.
Are you saying that if someone else had a checking account with the name Jeff Moden on it, that I shouldn't be allowed to use my name on a new checking account? Of course not... the surrogate key is the check account number and no effort to prevent duplicate names is required. Surrogate keys are the only thing that make that possible. Sure, you can do like this site did, but what a pain in the hiney if they did that on checking accounts.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2009 at 1:16 am
Jeff Moden (9/17/2009)
Are you saying that if someone else had a checking account with the name Jeff Moden on it, that I shouldn't be allowed to use my name on a new checking account? Of course not... the surrogate key is the check account number and no effort to prevent duplicate names is required. Surrogate keys are the only thing that make that possible. Sure, you can do like this site did, but what a pain in the hiney if they did that on checking accounts.
No, I'm not saying there can only be one checking account with the name Jeff Moden. If you went to the bank to deposit $10,000, you would want to make sure it went into the account of the right Jeff Moden. In this case, account number is a perfectly good business key and can be used to ensure you credit the account of the right Jeff Moden, because it is visible to the users of the system. You would put a unique constraint on account number to make sure 2 people don't end up with the same account number.
In the OP's system, what is it that would distinguish 2 operators with the name Jeff Moden?
What I am saying is, that whatever is used to distinguish the 2 apart must be the the primary key or the subject of a unique constraint.
If the Operator Id was something like an employee number, that was known to everybody, that would be fine. But in this case, it isn't, and nobody will ever know what someone's id is, so there has to be another candidate key in the data to ensure uniqueness.
September 19, 2009 at 11:35 am
Jeff Moden (9/17/2009)
the surrogate key is the check account number
An account number is not a surrogate key. It's a business key, i.e. it identifies something in the business domain outside the database. I do prefer the term "business key" to "natural key" but they mean the same thing.
I jumped in at this point because this thread may be one example of the problems caused by a "primary key" mindset. The important question is not "what should be the primary key?" but "what are the business rules?". That's why I deplore the primary key concept, which I'd like to see abolished. See: http://www.sqlservercentral.com/Forums/Topic783052-373-1.aspx
September 19, 2009 at 1:07 pm
David Portas (9/19/2009)
The important question is not "what should be the primary key?" but "what are the business rules?". That's why I deplore the primary key concept, which I'd like to see abolished. See: http://www.sqlservercentral.com/Forums/Topic783052-373-1.aspx
I've got to say, I thoroughly disagree. The important question when designing a business system is 'what are the business rules?'. When designing the database to run that system, questions then become those that relate to database design. Primary, unique and foreign keys are intensely pertinent to database design, and to suggest they are not... well, I won't comment, but I will just say that I disagree. A database is not a system in and of itself, a database is something which models the data storage and retrieval requirements of a system.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 19, 2009 at 1:55 pm
Matt Whitfield (9/19/2009)
I've got to say, I thoroughly disagree. The important question when designing a business system is 'what are the business rules?'. When designing the database to run that system, questions then become those that relate to database design.
A database design is usually required to model some portion of reality - i.e. the business domain. Certainly business rules are important if you want to model the business accurately. Techniques like Object-Role modelling (Halpin) derive the database design directly from a conceptual model based on business rules.
Primary, unique and foreign keys are intensely pertinent to database design, and to suggest they are not... well, I won't comment, but I will just say that I disagree.
Of course I agree that candidate keys and foreign keys are important in database design. The practice of designating one candidate key to be "primary" however is essentially "arbitrary" (to quote E.F.Codd who invented the term "primary key") or a matter of "purely psychological" convenience (Chris Date's opinion). Since a primary key makes no practical difference either in database design or in SQL systems the concept can safely be discarded or ignored. What are your reasons for thinking a primary key might be important? What difference would it make with or without one?
September 19, 2009 at 2:03 pm
David Portas (9/19/2009)
Jeff Moden (9/17/2009)
the surrogate key is the check account numberAn account number is not a surrogate key. It's a business key, i.e. it identifies something in the business domain outside the database. I do prefer the term "business key" to "natural key" but they mean the same thing.
I jumped in at this point because this thread may be one example of the problems caused by a "primary key" mindset. The important question is not "what should be the primary key?" but "what are the business rules?". That's why I deplore the primary key concept, which I'd like to see abolished. See: http://www.sqlservercentral.com/Forums/Topic783052-373-1.aspx
With those kinds of semantics at your beckon-call, I understand why you deplore the Primary Key concept and truly hope that your wish never comes to pass because, for all the reasons stated on this and other threads, I disagree with you. š
--Jeff Moden
Change is inevitable... Change for the better is not.
September 19, 2009 at 2:18 pm
Jeff Moden (9/19/2009)
With those kinds of semantics at your beckon-call
What do you mean by that? You have a different understanding of what a "surrogate key" is? Then please share it with us. What I said is what Codd, Date and most eveyone I know defines a surrogate key to be, i.e. something which has no external meaning in the domain of discourse, i.e. not a business key.
I understand why you deplore the Primary Key concept and truly hope that your wish never comes to pass because, for all the reasons stated on this and other threads, I disagree with you. š
Because you think it has entertainment value? I see what you mean... (unfortunately) š
September 19, 2009 at 2:28 pm
David Portas (9/19/2009)
Of course I agree that candidate keys and foreign keys are important in database design. The practice of designating one candidate key to be "primary" however is essentially "arbitrary" (to quote E.F.Codd who invented the term "primary key") or a matter of "purely psychological" convenience (Chris Date's opinion). Since a primary key makes no practical difference either in database design or in SQL systems the concept can safely be discarded or ignored. What are your reasons for thinking a primary key might be important? What difference would it make with or without one?
Because computing is, for the most part, about psychological convenience. Documentation is a psycholgical convenience if you want to get down to it. Yes, you could say let's just use unique keys - but I think the distinction can be particularly useful - especially for someone coming onstream with a complex database schema. You could be a c programmer and quote k&r at me - at which point I would remind you that it's rare to find a straight k&r compiler wheras ansi ones are two a penny. And it's the same for me with date and codd - where's the database system they built?
And that's what it comes down to for me - reality versus theory. Abstraction is an incredibly useful concept in computing in general, and I believe it is just as appropriate in database parlance - thus my distinction in my previous post between a business system and the implementation of it. A database concerns itself with implementation, implementation is maintained by humans, and humans need all the psychological convenience that they can get...
Apologies for my appalling capitalisation, btw, am typing on a failphone.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 19, 2009 at 2:51 pm
Fair points Matt. So to extend the psychological convenience argument, if I find it convenient NOT to have a primary key because in some particular case I don't need to designate any one key as special in any way then it's just as reasonable to leave it out as to declare one. Either way, selecting a primary key is not of great fundamental or practical importance. To use the word you used, it may be pertinent to some people who use the system but it is certainly not essential.
Even granted this "convenience" factor I would still question the wisdom of having a special syntax for a "PRIMARY KEY". A note in a data model document or data dictionary instead could be just as useful.
September 19, 2009 at 2:56 pm
I'm not going to come down on one side or the other on the PRIMARY KEY debate... as far as I can see, it's a question of semantics... the purpose is the same.
The point I was trying to make to the OP is that you shouldn't rely on just a surrogate key to uniquely identify a row in a table. It doesn't matter who states the rule, every seasoned data analyst will tell you that there must be a business/natural key that unquely identifies the data as far as the business is concerned or you are leaving yourself open to duplicate data.
Whether that is defined by saying PRIMARY KEY or NOT NULL UNIQUE, I don't mind. I can create a foreign key reference to either.
September 20, 2009 at 6:30 am
David Portas (9/19/2009)
Fair points Matt. So to extend the psychological convenience argument, if I find it convenient NOT to have a primary key because in some particular case I don't need to designate any one key as special in any way then it's just as reasonable to leave it out as to declare one. Either way, selecting a primary key is not of great fundamental or practical importance. To use the word you used, it may be pertinent to some people who use the system but it is certainly not essential.
No, certainly not essential, agree whole-heartedly. I think though, if you find it convenient NOT to have a PK, then you can just leave one out - though I think that's a different thing to advocating complete abolishment... I will say about one case in my DBA history though - whereby I was asked to go and help with a database whereby somebody had used a delete and forgotten the WHERE clause - normal kind of thing - but in this case the data was a nightmare because there were no unique keys of any kind - not PK or UQ. So, in those sort of cases, I would find a case for enforcing a PK on every table (or, since they are semantically equivalent, enforcing *at least one unique constraint*).
David Portas (9/19/2009)
Even granted this "convenience" factor I would still question the wisdom of having a special syntax for a "PRIMARY KEY". A note in a data model document or data dictionary instead could be just as useful.
I think the syntactic and implementation differences between unique and primary key constraints are actually very minimal (i.e. the word 'UNIQUE' .v. the words 'PRIMARY KEY'). A note in a data dictionary / model is good - but I've come across plenty of databases/situations where asking for a data dictionary resulted in a puzzled and slightly vacant look š
But anyway, I'd just like to say thanks for your well thought out argument/opinion - I am always grateful for a thread where I come away more knowledgeable, and you have ensured that is the case this time. Thank you š
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 20, 2009 at 3:51 pm
David Portas (9/19/2009)
Jeff Moden (9/19/2009)
With those kinds of semantics at your beckon-callWhat do you mean by that? You have a different understanding of what a "surrogate key" is? Then please share it with us. What I said is what Codd, Date and most eveyone I know defines a surrogate key to be, i.e. something which has no external meaning in the domain of discourse, i.e. not a business key.
I understand why you deplore the Primary Key concept and truly hope that your wish never comes to pass because, for all the reasons stated on this and other threads, I disagree with you. š
Because you think it has entertainment value? I see what you mean... (unfortunately) š
Heh... no... I meant you calling a name one thing in one table and something else in a different table.
My point is that names make terrible keys (natural or otherwise) in any table especially if you have to do updates or deletes. Unless something like an ISO country/state/province abbreviation is used, most names of things just aren't unique enough to even consider as a key.
Although Codd and Date certainly had some great ideas, just because a famous person says something, it doesn't mean that they're right in all cases. If I'm not mistaken, Codd also said that surrogate keys were ok so long as they weren't exposed to the users. I suppose that's mostly true except when it comes to things like customer account numbers and the like except that you don't semantically agree that those are nothing more the surrogate keys.
I believe there are probably as many "experts" that think everything should have a surrogate key as there are folks like you that think there shouldn't be anything but natural keys. I've done it both ways depending on the data and what the business rules for treating the data are. And that's the real point I'm trying to make... it's nice to have a choice. When I said I disagree with you, I mean that I disagree with the idea of abolishment. š
Heh... and yeah... I do find it all entertaining. š
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply