January 2, 2013 at 1:31 pm
Hello all,
I have an access database using sql server as the backend. It has been very simple in design but as time moves on designs need to change.
I am looking to add several tables to the database in order to capture technicians who have worked on cases and the physical sites where the case originated.
The complicated part has been created a set of phone number tables. I will have a need to capture detailed information on the phone numbers of technicians, who may have multiple phone numbers, and I need to at least capture one phone number for the site location.
There will be a TechnicianContact table with a TechPhoneID column that relates to a lookup table named PhoneContact.
There will be a Site table with a column named SitePhoneNumber that relates to the same lookup table named PhoneContact.
The PhoneContact table will relate to the PhoneNumbers table
The PhoneNumbers table will relate to a PhoneType table
The PhoneType table will be a category table which can be used to determine if the phone number is to a technician, a site, or future uses.
I have included the Create Table script below and would like some of your opinions.
Please let me know what you think.
USE GKHELPDESK;
GO
SET NOCOUNT ON;
-- Create Technician and Site Tables
--how would i represent multiple phone numbers for a technician and site manager in a flexible table
CREATE TABLE dbo.Technician
(
TechId NVARCHAR(8) PRIMARY KEY NOT NULL
,TechFirstName NVARCHAR (50) NOT NULL
,TechLastName NVARCHAR (50) NOT NULL
,TechTitle NVARCHAR (50) NOT NULL
,TechModifiedDate DATE NOT NULL
);
/* Phone type column will have a foreign key relationship to a type table to indicate the contact type. Such as a technician, site manager or future categories
*/
CREATE TABLE dbo.PhoneNumbers
(
PhoneID int Primary Key NOT NULL
,PhoneCountryCode NVARCHAR(3) NULL --Covers country codes if not in U.S.
,PhonePrefix1 NVARCHAR(4) NOT NULL --Covers NPA and City/Area Codes
,PhonePrefix2 NVARCHAR(4) NOT NULL --Covers Switch Prefix
,PhoneNumber NVARCHAR(15) NOT NULL --Actual line number or remaining numbers
,PhoneExtension NVARCHAR(10) NULL --Phone extension to a PBX if applicable
,PhoneContactType int NOT NULL --ie, site manager, technician, future types
,PhoneContactID NVARCHAR (8) NOT NULL -- will be the parent to the id of the respective contact
,
);
--Phone type table for the PhoneNumbers table FK relationship to determine the user type, ie tech
CREATE TABLE dbo.PhoneType
(
PhoneTypeID int Primary Key Not NUll
,PhoneTypeDescription NVARCHAR (30) Not Null --ie, Technician, Site, Future Uses...
);
--Phone contact table to relate the phone number to the contact
CREATE TABLE dbo.PhoneContact
(
PhoneContactId NVARCHAR (8) PRIMARY KEY NOT NULL
,PhoneId int NOT NULL
);
CREATE TABLE dbo.TechnicianContact
(
TechID NVARCHAR (8) PRIMARY KEY NOT NULL
,TechAddressLine1 NVARCHAR (60)NOT NULL
,TechAddressLine2 NVARCHAR (60)NULL
,TechCity NVARCHAR (30)NOT NULL
,TechStateProvinceID INT NOT NULL
,TechContactModifiedDate Date NOT NULL
,TechPhoneID int NOT NULL
);
CREATE TABLE dbo.Site
(
SiteID NVARCHAR (8) PRIMARY KEY NOT NULL
,SiteName NVARCHAR (25) NOT NULL
,SiteAddressLine1 NVARCHAR (60) NOT NULL
,SiteAddressLine2 NVARCHAR (60) NOT NULL
,SiteCity NVARCHAR (30) NOT NULL
,SiteStateProvinceID INT NOT NULL
,SiteModifiedDate DATE NOT NULL
,SiteManagerName NVARCHAR(60) NOT NULL
,SitePhoneNumber int not null
);
GO
January 2, 2013 at 2:28 pm
I've found it useful for this kind of thing to have a PhoneNumbers table, with the relevant data for that, including country code, etc., and an ID number, and a type that indicates phone, fax, or both, or cell phone. Then have join tables from people (technicians in your case) to that table, and put data in the join table about whether it's a personal number, a work number, a daytime-only number, an evenings-only number, that kind of thing, in the join table. Then another join table to the phones, from your sites/offices table.
That way, if a phone is useful for contacting an office during the day, but not at night, you can indicate that. Same phone might be useful as a fax at night, for example. Or another number might be office during the day, emergency-only at night (for a home office). Also, more than one person might be contactable with the same fax machine. Or within an office, you might have several faxes, with one for general data, and other ones only useful for certain people.
That allows for maximum flexibility. Usually, you'll find that you end up needing that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 2, 2013 at 5:52 pm
CELKO (1/2/2013)
ACCESS is a disaster and you need to get rid of it. I saw its premiere at a COMDEX; it did not work then and it does not work now.
I think someone needs to tell the thousands of companies worldwide that use it for hundreds of thousands of applications that.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 2, 2013 at 6:37 pm
Joe,
You asked?
Have you looked at the international phone number standards? Do your columns have a regular expression constraint on the columns?
I did a little bit of research for international phone numbers and with the columns I have created it seems as if I would be able to accommodate many scenario's. What do you think I am missing?
In regards to the expression constraint are you referring to a constraint to a check constraint such as this....?
Check (PhonePrefix1 LIKE '[0-9][0-9][0-9][0-9]')
You also wrote
You have a singular “phone_contact” table name!! ONLY One?!
I take that this is a suggestion to make the phone_contact table a wider table to include the other columns I am creating in separate tables.
Why would I want to denormalize?
I do agree that the technician nvarchar lengths are to long and will adjust those to the suggested lengths. I actually had no idea the USPS posted any documentation on name lengths. I'll look that up later to see if there are other documented lengths.
You wrote
You put audit date in a base table! NO! NO! Sorry, without a key, this mess is not a table by definition. Let me repeat that, by definition!!
I believe your referring to the dbo.Technician table. I did put the Tech_ID column as the primary key. Also what problem does a TechModifiedDate column pose? What technique do you use to know when a record has been changed?
You wrote
The rest of your posting is totally wrong; not a little wrong, but fundamentally, totally wrong.
Aside from some of the points you already made and if I took the 3 phone number related tables I scripted out and condensed them into one table, removed the date auditing, what other issues do you find ?
January 2, 2013 at 6:47 pm
Joe - Please excuse me for jumping in and interpreting for you. If you feel I'm in error, I know you won't hesitate to correct me. 😀
kwoznica (1/2/2013)
Joe,You asked?
Have you looked at the international phone number standards? Do your columns have a regular expression constraint on the columns?
I did a little bit of research for international phone numbers and with the columns I have created it seems as if I would be able to accommodate many scenario's. What do you think I am missing?
In regards to the expression constraint are you referring to a constraint to a check constraint such as this....?
Check (PhonePrefix1 LIKE '[0-9][0-9][0-9][0-9]')
I believe Joe was suggesting that your application apply a Regular Expression audit to the phone numbers (Google "Regular Expression" or "RegEx"). This is far more complex than the simple LIKE check constraint you've proposed, although there are probably sources that can provide you with the RegEx string you'd need to use.
kwoznica (1/2/2013)
You also wroteYou have a singular “phone_contact” table name!! ONLY One?!
I take that this is a suggestion to make the phone_contact table a wider table to include the other columns I am creating in separate tables.
Why would I want to denormalize?
"Normalize until it hurts. Denormalize until it works." is a quote I heard somewhere that I believe is quite appropriate.
kwoznica (1/2/2013)
I do agree that the technician nvarchar lengths are to long and will adjust those to the suggested lengths. I actually had no idea the USPS posted any documentation on name lengths. I'll look that up later to see if there are other documented lengths.You wrote
You put audit date in a base table! NO! NO! Sorry, without a key, this mess is not a table by definition. Let me repeat that, by definition!!
I believe your referring to the dbo.Technician table. I did put the Tech_ID column as the primary key. Also what problem does a TechModifiedDate column pose? What technique do you use to know when a record has been changed?
Lot's of people do this and the deficiency is that all you can tell is the last change. If you need to track all changes, you'd need to put the audit trail into a separate table. You also can't tell by the single date what information on the record was changed.
kwoznica (1/2/2013)
You wroteThe rest of your posting is totally wrong; not a little wrong, but fundamentally, totally wrong.
Aside from some of the points you already made and if I took the 3 phone number related tables I scripted out and condensed them into one table, removed the date auditing, what other issues do you find ?
No comment on the last one because it is too open ended.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 3, 2013 at 7:20 am
From what I can find, the longest last name on record, is:
Wolfeschlegelsteinhausenbergerdorffvoralternwarengewissenhaf
tschaferswesenchafewarenwholgepflegeundsorgfaltigkeitbeschut
zenvonangereifenduchihrraubgiriigfeindewelchevorralternzwolf
tausendjahresvorandieerscheinenbanderersteerdeemmeshedrraums
chiffgebrauchlichtalsseinursprungvonkraftgestartseinlangefah
rthinzwischensternartigraumaufdersuchenachdiesternwelshegeha
btbewohnbarplanetenkreisedrehensichundwohinderneurassevanver
standigmenshlichkeittkonntevortpflanzenundsicherfreunanleben
slamdlichfreudeundruhemitnichteinfurchtvorangreifenvonandere
rintlligentgeschopfsvonhinzwischensternartigraum
Yeah, that's one name. But you probably don't need to accommodate that.
On the other hand, the USPS standard for names is simply how long their routing automation equipment can handle, and that what qualifies for reduced-rate postage. It's not a rule for anything else. It has known problems with names from France, Spain, India (and surrounding areas), and a few others, when they go beyond "normal American name-length". Go ahead and use that rule if you so desire, but keep in mind that you're using a standard that was never meant to be used in that manner. Kind of like using inches (or centimeters) to measure mountains. Can be done, but not really intended for it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 3, 2013 at 11:12 am
CELKO ACCESS is a disaster and you need to get rid of it. I saw its premiere at a COMDEX; it did not work then and it does not work now.
Sure it works Joe. Stop trying to feed it punch cards, and try using VBA on it instead of COBOL.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 3, 2013 at 11:19 am
CELKO The rest of your posting is totally wrong; not a little wrong, but fundamentally, totally wrong.
The rest *attitude*, of your postings Joe, is totally wrong; not a little wrong, but fundamentally, totally wrong.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 3, 2013 at 12:15 pm
kwoznica (1/2/2013)
You also wroteYou have a singular “phone_contact” table name!! ONLY One?!
I take that this is a suggestion to make the phone_contact table a wider table to include the other columns I am creating in separate tables.
Why would I want to denormalize?
You wouldn't.
What Joe's cryptically saying is that as far as he's concerned, table names absolutely, must, always, in every circumstance, without exception be plural (phone_contacts) and if you ever dare to have a singular name for a table, lightning will strike, earth will quake, plague will descend upon you and you will have to retreat into a desert and become a hermit because no one would ever let you work on their systems again. 🙂
The rest of us don't care what you name your tables, as long as you're consistent about it and the names are descriptive (all plurals, all singular, all prefixed, non prefixed, your choice). I personally go for pluralised names, no prefixes, but that's just my preference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 3, 2013 at 12:35 pm
GilaMonster (1/3/2013)
kwoznica (1/2/2013)
You also wroteYou have a singular “phone_contact” table name!! ONLY One?!
I take that this is a suggestion to make the phone_contact table a wider table to include the other columns I am creating in separate tables.
Why would I want to denormalize?
You wouldn't.
What Joe's cryptically saying is that as far as he's concerned, table names absolutely, must, always, in every circumstance, without exception be plural (phone_contacts) and if you ever dare to have a singular name for a table, lightning will strike, earth will quake, plague will descend upon you and you will have to retreat into a desert and become a hermit because no one would ever let you work on their systems again. 🙂
The rest of us don't care what you name your tables, as long as you're consistent about it and the names are descriptive (all plurals, all singular, all prefixed, non prefixed, your choice). I personally go for pluralised names, no prefixes, but that's just my preference.
Since there is no true consensus on this, just be consistent, except don't use prefixes: the expert consensus is almost 100% against that.
FWIW, plurals seem to be the de facto consensus, unfortunately (witness the internal system tables). So I use plural table namess, although I personally believe singular is much more technically accurate.
CELKO seemed to imply that it must be plural because there are multiple rows in the table. Hmm, but what about table(s) with only ONE row in them? So, CELKO, would one-row table names be singular or plural? Would the name have to change when rows are added or removed!? Proof enough that what he said was a stupid "reason" for choosing one or the other. Meaning it can't be the (ultimate) number of rows in the table that determines the name.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 3, 2013 at 12:37 pm
.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply