September 15, 2009 at 1:42 pm
Hello Everyone,
I planned to convert Microsoft Excel spreadsheet (Ticketing Systems) into C# application and utilize MS SQL Server 2008.
Enclosed is my database scripts:
-- Create Operators Table
CREATE TABLE Operator (
OperatorID CHAR(10) NOT NULL PRIMARY KEY CLUSTERED,
OpsFirstName CHAR(35) NOT NULL,
OpsMiddleName CHAR(2) SPARSE NULL,
OpsLastName CHAR(35) NOT NULL,
CompanyName CHAR(75) SPARSE NULL,
OfficePhone VARCHAR(20) SPARSE NULL,
MobilePhone VARCHAR (20)SPARSE NULL );
GO
--Create Caller Table
CREATE TABLE Caller (
CallerID CHAR(10) NOT NULL PRIMARY KEY CLUSTERED,
SiteID CHAR(10) NOT NULL,
CallerFirstName CHAR(35) NOT NULL,
CallerMiddleName CHAR(2) sparse NULL,
CallerLastName CHAR(35) NOT NULL,
OfficePhone VARCHAR(20) SPARSE NULL,
MobilePhone VARCHAR(20) sparse NULL);
GO
--Create Site Table
CREATE TABLE Site (
SiteID CHAR(10) NOT NULL PRIMARY KEY CLUSTERED,
SiteName CHAR (100) NOT NULL,
TicketID VARCHAR(10) NOT NULL);
GO
-- Create Crew Table
CREATE TABLE crew (
CrewID CHAR(10) NOT NULL PRIMARY KEY CLUSTERED,
FirstName CHAR(35) NOT NULL,
MiddleName CHAR(2) SPARSE NULL,
LastName CHAR(35) NOT NULL,
OfficePhone VARCHAR(20) SPARSE NULL,
MobilePhone VARCHAR(20) SPARSE NULL)
GO
-- Create Turbine Table
CREATE TABLE Turbine (
TurbineID VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED,
TurbineQuantity SMALLINT NOT NULL,
TurbineStatus CHAR(100) NOT NULL,
ClearanceNumberStopTime DATETIME2 NOT NULL,
ClearanceNumberStartTime DATETIME2 NOT NULL,
ClearanceNumberReleaseNotes VARCHAR(5) NOT NULL);
GO
-- Create LockOutTagOut
CREATE TABLE LockOutTagOut (
TicketID VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED,
FaultDescription NVARCHAR(255) SPARSE NULL,
CallerDate DATETIME2 NOT NULL,
OperatorID CHAR(10) NOT NULL,
CrewID CHAR(10) NOT NULL,
CallerID CHAR(10) NOT NULL,
TurbineID VARCHAR(10) NOT NULL);
GO
ALTER TABLE Caller
ADD CONSTRAINT fk_Site FOREIGN KEY (SiteID)
REFERENCES Site(SiteID);
GO
ALTER TABLE Site
ADD CONSTRAINT fk_LockOutTagOut FOREIGN KEY (TicketID)
REFERENCES LockOutTagOut(TicketID);
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_Crew FOREIGN KEY (CrewID)
REFERENCES Crew (CrewID);
GO
ALTER TABLE LockOutTagOut
ADD CONSTRAINT fk_Turbine FOREIGN KEY (TurbineID)
REFERENCES Turbine(TurbineID);
GO
Can someone please review and provide me advice?
Notes: Business issues on Caller tables. The Caller will resign/change jobs every week.
How to resolve this issues and can anyone advice on this?
Thanks in advance.
Edwin
Thank in advance.
Edwin
September 15, 2009 at 2:53 pm
I have two things to say:
1) You seem to have all your primary keys as Char() fields. Unless there's a compelling reason for that, go with int, or bigint if any of your tables will hold more than 2 billion rows.
Reason being is that ints, like in c#, vb etc are much faster to compare than strings.
You might want to consider making the PKs IDENTITY fields. I certainly almost always use an IDENTITY field for a PK - simply because if you have an IDENTITY field on a table you are guaranteed to have something unique by which to identify rows (this comes in handy when somebody helpfully drops your PKs for you - experience tells me this).
2) The elves inside SQL Server don't like the fact that one of your tables doesn't have the same capitalisation as the others. Keep the elves happy and they will sort you out. 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 15, 2009 at 4:01 pm
Hi Matt,
Thanks for your advice. I still tested and debug.
I changed the Operator, Site and Crew, Operator, and Callelr table's primary key to smallint with IDENTITY.
Typo on Caller table 🙂
Best regards,
Edwin
September 15, 2009 at 4:05 pm
No worries.
Bear in mind smallint will only do you for up to 32,767 records before the IDENTITY field wraps round to -32,768.
Use smallint only if you are *sure* the DB won't be filled with much data.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 15, 2009 at 4:29 pm
I was considering the data size when I designed it the first time. The data can grow in 100K++ in couple months.
That’s why I did not use smallint (-32, 768 to 32,767) even Smallinit is used very small storage space (2bytes).
Any suggestions on this?
September 15, 2009 at 4:45 pm
Yep - go with int like I originally said 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 16, 2009 at 2:33 am
This bit of advice goes for all tables, but I'll illustrate it with this table
CREATE TABLE Operator (
OperatorID CHAR(10) NOT NULL PRIMARY KEY CLUSTERED,
OpsFirstName CHAR(35) NOT NULL,
OpsMiddleName CHAR(2) SPARSE NULL,
OpsLastName CHAR(35) NOT NULL,
CompanyName CHAR(75) SPARSE NULL,
OfficePhone VARCHAR(20) SPARSE NULL,
MobilePhone VARCHAR (20)SPARSE NULL );
GO
OperatorID is your primary key, but this is a surrogate key, not the real primary key from a business point of view.
I would hazzard a guess that OpsFirstName and OpsLastName (and possibly CompanyName) would be how you would uniquely identify an operator from a business perspective, so you should add a unique constraint on those columns that make up this key.
To illustrate further, I'm guessing that you will have a dropdown box somewhere in your application listing the Operators. You won't list the OperatorId in this dropdown list, more likely OpsFirstName and OpsLastName. What if I was to add 2 entries to your operators table, both with the name "Joe Bloggs" (but different OperatorIDs), how would your users be able to tell the difference between them?
September 16, 2009 at 8:35 pm
Ian Scarlett (9/16/2009)
What if I was to add 2 entries to your operators table, both with the name "Joe Bloggs" (but different OperatorIDs), how would your users be able to tell the difference between them?
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2009 at 8:38 pm
Edwin,
What happens if a person has more than one mobile phone? The way you're tables are constructed, you're pretty much screwed. The phone numbers need to be in a separate table with start and end dates in case people change numbers.
Use that as a "hint" for similar normalizations. For example, you should not have a company name for each individual... what if a person works for more than one company like I do? What if the company name changes?
Do a Google search for normalization and look at your tables again.
And don't let anyone scare you off from using surrogate keys like OperatorID. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2009 at 4:33 am
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.
September 17, 2009 at 5:41 am
Ian Scarlett (9/17/2009)
I could add Joe Bloggs at 1 The Street from Acme Co more than once, and you still can't distinguish them.
It could happen - more than one person with the same name living in the same place working for the same company. Wearing the same t-shirt 😀
Sorry - couldn't resist!
But I have to say I do agree - uniqueness should be enforced if the business rules surrounding the data prescribe it. However, given that this is a 'better implementation of existing system' - it's quite possible that the duplication exists already...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 17, 2009 at 5:56 am
Matt Whitfield (9/17/2009)It could happen - more than one person with the same name living in the same place working for the same company. Wearing the same t-shirt 😀
Indeed it could, but would they be wearing the same colour underpants?:-D
As well as uniquely identifying someone, it also serves to stop the same person being added twice by mistake.
I have seen this happen with something as simple as a list of country codes/country names. Someone added the same country name to a table with a different country code (code being the PK). You then end up with the same country appearing twice in dropdown boxes... users then have to remember to always select the one that appears first... as long as the dropdown box has been populated from the database using an ORDER BY clause!
September 17, 2009 at 6:04 am
Ian Scarlett (9/17/2009)
I have seen this happen with something as simple as a list of country codes/country names. Someone added the same country name to a table with a different country code (code being the PK). You then end up with the same country appearing twice in dropdown boxes... users then have to remember to always select the one that appears first... as long as the dropdown box has been populated from the database using an ORDER BY clause!
I authored the database and management back end for a major oil company's global loyalty scheme (contractually bound not to say which one) - and they had a similar problem with their sites. They have sites which were owned by dealers, and when a dealer sold their site, it created a new 'site validity period'. That led to almost exactly the same issue. Luckily I wrote the web app so that it formatted choices from FK referenced tables with a 'candidate signature' which was also in the DB - so I just added the start / end dates - problem solved. But you're definitely right, it can cause fun & games when two options 'seem the same'...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 17, 2009 at 7:34 am
Hi Jeff,
The business rules did not allow two mobile phones in my case.
Thanks for your hints.
-Edwin
September 17, 2009 at 7:43 am
Edwin-376531 (9/17/2009)
The business rules did not allow two mobile phones in my case.
...yet
What Jeff says is solid. I was just too dumb to pick up on it. 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply