How would you handle this and why??? (Common Data)
-
I have a database and I am currently normalizing it but had a thought on a few piece that I wonder if anyone has used and what was their reasoning. Or if you would avoid completely and why. I have my reasonings for not using but am putting this out for public consumption.
I have a database with several bits of data relating to financial services. In the database there are tables for Financial Centers, Departments and Personnel. In each of theses tables there are multiple fields related to phone numbers.
Financial Centers have
Published (The phone book one)
Backdoor (Interoffice contact number)
Fax (well you can guess)
Departments have
Primary (Interoffice contact number)
Fax (again if you can figure it out)
adn
Personnel have
Work Phone (Their direct number)
Pager (Umbilical cords for potential emergencies and whatever contacts)
PIN (Pager related)
Cell Phone (Similar to a pager but with vocal interaction)
Now I of course break the tables to the individual 3 and have a phone table for each consisting of
FK_ParentTableID
FK_PhoneNumberType (another table with those lovely descriptions)
Phoner_Number (The number)
But I wonder how many people have thought about making a single Phone Numbers table with maybe a field
tbl_From
to know which table to link back too.
If anyone has done this, why. Or if you have thought about yourself but not used then why not.
For me either would work but I just choose not to use the common table for the sake of my coworkers and past experience with them trying to figure out what I am doing from the code (sorry I am ba about documentation but mostly because we are constantly bombarded with when will it roll out and when will you start this next project, working on this thou).
-
I could see this generating some interesting comments.
I cannot say which I prefer.
A previous employee built a database to hold info about our dealer network. This database has a table holding telephone nos, another holding a list of types of no (eg phone,fax etc) and a link table to join them to a dealer. Great when you want to add new types but horrible when you want to look from the telephone no table as you need the others to make sense of the number. In this case you have to join 4 tables to get on piddly piece of info but you get flexability.
This database also holds addresses for the dealer and has a similar structure (adress table,adress type table, link table) and this definately works better this way as it is possible for our dealers to use the same address for different types (main, parts, service) and there is a lot more info (adress lines, postcode). A while ago the company decided it wanted to have another type of address (correspondence), because the frontend was flexible in using the database, all I did was create a new address type and shazzamm, job done.
When I built another database to hold staff info I put all the telephone nos on one table as the list was predefined and not liable to change.
In conclusion, I think it depends on the business and the way the data will be accessed as to what level you denormalize to. To denormalise too far an atomic level can just as bad a not denormalising at all!
Confused!
I am and I wrote this!!!Far away is close at hand in the images of elsewhere.
Anon. -
quote:
Now I of course break the tables to the individual 3 and have a phone table for each consisting ofFK_ParentTableID
FK_PhoneNumberType (another table with those lovely descriptions)
Phoner_Number (The number)
But I wonder how many people have thought about making a single Phone Numbers table with maybe a field
tbl_From
to know which table to link back too.
If anyone has done this, why. Or if you have thought about yourself but not used then why not.
actually not really related to your question, but one thing that could be an issue is how to deal with missing information, eg NULLS. How many people will have multiple phone numbers (2, 3,....).
Maybe this http://www.hughdarwen.freeola.com/TheThirdManifesto.web/Missing-info-without-nulls.pdf is worth reading?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for the input,
Frank for Personnel they have the option of
Work Phone (Their direct number)
Pager (Umbilical cords for potential emergencies and whatever contacts)
PIN (Pager related)
Cell Phone (Similar to a pager but with vocal interaction)
but only one of each with one to all of them and the app handles the nulls fine (the app has all 4 fields but if the value is null or does not exist it will ignore also the layout of the page is condusive to these coming out as the next recordset after the base info). I also don't want to create the excess storage space and nor do I want in a varchar field since an int is 4 bytes and varchar in this case would have to be 10 (plus 2 for each varchar with data in it to point to the byte number for the start of that varchar column). Plus I am trying to normalize this db better than the previous versions (this was my first project and is now 4 years old in production).
-
I would strongly consider using a single table. Maybe not for phone numbers, but if you want to add any kind of business logic to such a table, you would need to reproduce all of the procedures etc. for each table.
Like you said Antares, the code to fetch anything from this design gets a bit more obfuscated.
-
quote:
Thanks for the input,Frank for Personnel they have the option of
Work Phone (Their direct number)
Pager (Umbilical cords for potential emergencies and whatever contacts)
PIN (Pager related)
Cell Phone (Similar to a pager but with vocal interaction)
but only one of each with one to all of them and the app handles the nulls fine (the app has all 4 fields but if the value is null or does not exist it will ignore also the layout of the page is condusive to these coming out as the next recordset after the base info). I also don't want to create the excess storage space and nor do I want in a varchar field since an int is 4 bytes and varchar in this case would have to be 10 (plus 2 for each varchar with data in it to point to the byte number for the start of that varchar column). Plus I am trying to normalize this db better than the previous versions (this was my first project and is now 4 years old in production).
one telefonnumber only. This is really great. I have three numbers here and depending on which one rings I must speak the appropiate company slogan
If it is 4 years in production, it seems to work. Why do you want to change this? Or is this rather academical?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Performance and no this is not the way it is, a lot of junk over the years got added after initially built and I know it is not up to par on design or coding. So I wanted to go back and rebuild correctly and improve the performance. Plus I got a recent list of new additions and I want the users to be able to maintain access to the website and the support tables themselves so I can get that off my back. And I really have a lot of cool new coding things to go with it to make this site take the top spot on the potential for being the only corporate DB for this info if I can build the audience a bit.
-
quote:
and I want the users to be able to maintain access to the website and the support tables themselves so I can get that off my back.sure if this is a good idea???
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I'm all for empowering the user. As long as it is in a controlled environment.
Far away is close at hand in the images of elsewhere.
Anon. -
quote:
I'm all for empowering the user. As long as it is in a controlled environment.yes, everyone should be treated the same. Make them all sysadmin, for they know what they do!!
Amen,
time to buy my first dishwasher ever today!
Good evening!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
yes, everyone should be treated the same. Make them all sysadmin, for they know what they do!!Always the cynic Frank.
quote:
time to buy my first dishwasher ever today!Is'nt that why you got married?
Far away is close at hand in the images of elsewhere.
Anon. -
quote:
I'm all for empowering the user. As long as it is in a controlled environment.Hehehe, to all yes it is controlled. I just want the key folks who give access and taketh it away to be able to do it themselves. Those logins are for SQL server they are table lookups and based on a LDAP login for the site. I have built in autoexpiring accounts and the ability to add users who will come in in the future and those who have a leave date. You can put them on temporary leave and the LDAP forces password change every 90 days. Also I have a control table which gives details about each support table to generate a single update page where a user can change the title values and some other times or make active/inactive and all items are audited. Of course all SP based and the accounts for the website itself has no table/view level access. So I think I have convered all the basics and I have gotten rid of the dredded dynamic SQL that I built in the original site.
-
Well I'll step in a little late here...
I would go with the single phone table but I wouldn't bother with the tbl_from field.
As a side note: I would probably create some UDF's or views that would return the most used phone types so that you can easily join to them for when you want to display the phone numbers. This makes it easy to read your sql and thus easier to figure out what is going on. 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
quote:
Well I'll step in a little late here...I would go with the single phone table but I wouldn't bother with the tbl_from field.
As a side note: I would probably create some UDF's or views that would return the most used phone types so that you can easily join to them for when you want to display the phone numbers. This makes it easy to read your sql and thus easier to figure out what is going on. 🙂
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Unfortunately this will be on a SQL 7 server so UDFs are out. However you state you wouldn't use the tbl_from field, the only thing is how would I know which table it is associated with if I use a combined table. I have to be able to ID the values (especially since the primary key values of the other tables are INTs, yes I am looking to get away from an IDENTITY column but I haven't completed the design yet).
-
quote:
Always the cynic Frank.this is most of the time as VERY useful attitude ?!?
quote:
Is'nt that why you got married?Nope, I've been the dishwasher for two people, three people and now with four people I had enough
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
I am and I wrote this!!!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply