Database

David Mujica22-Jul-10 10:56

David Mujica

22-Jul-10 10:56

My vote is to leave the 3 telephone attributes in the Customer Record and allow for blank/Null numbers.
There is an argument for keeping things simple and in this case I would go for simplicity. Cool | :cool:

Euhemerus22-Jul-10 11:05

22-Jul-10 11:05

Thanks for the advice. My college lecturer would be spitting in his beer if he even knew I was asking the question! However, having been taught the 'correct way' I'm wondering what real database designers ACTUALLY do; whether they stick to the theory or compromise.

There is only one satisfying way to boot a computer.

Mycroft Holmes22-Jul-10 14:21

Mycroft Holmes

22-Jul-10 14:21

In most business cases I would go for the denormalised structure as David suggested.

However I have seen this type on Nth level normalisation work quite well, especially if it is a commercial product and must be extensible to meet user requirements. It also requires a GREAT support team and constant retention of the IP in the development team.

Corporate products can accept greater restrictions on their boundaries. If you want 4 phone number then stick it in Outlook, bugger off.

Never underestimate the power of human stupidity
RAH

Goutam Patra22-Jul-10 22:00

Goutam Patra

22-Jul-10 22:00

I also agree with what David said

R. Giskard Reventlov22-Jul-10 22:43

R. Giskard Reventlov

22-Jul-10 22:43

I would take the view that people may have more than 3 contact numbers (attributes) and by normalising out you are allowing for any number of numbers, so to speak, and building in the ability to add as many as required in the future. On the other hand if 3 numbers is set in stone (as if anything ever really is) then leave as is.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Eddy Vluggen22-Jul-10 23:16

Eddy Vluggen

22-Jul-10 23:16

Your teacher is right, 3NF would be the minimum requirement before I'd call it a well-designed database. You are right too; in the real world, databases are created when needed, and grow over time - not all of them are designed upfront.

Whenever I can, I try and normalize. Your example would make it very hard to get the complete address-details of the customer, since you'd be seeing a similar construction with ZipCodes and streets.

Digital Thunder wrote:
would you leave the three different telephone attributes in the customer table and invite null entries?

You don't want to do this;

SQL

SELECT ([phone1], [phone2], [phone3]) from ..

Before you know it, customers will want to add extra information, like a short description (e.g. "this number only between 1 and 5 am") or a bool column to flag disconnected lines.

That would lead to a

SQL

SELECT ([phone1], [phone1desc], [phone2], [phone2desc], [phone3] ..

Might not sound very useful in this particular example, but I'm sure that most of us had to update a similar database at least once in their career. Imagine you have a table that all of a sudden would receive four or five more attributes Smile | :)

I are Troll Suspicious | :suss:

Euhemerus23-Jul-10 3:09

23-Jul-10 3:09

Eddy Vluggen wrote:
You don't want to do this;

SELECT ([phone1], [phone2], [phone3]) from ..

Before you know it, customers will want to add extra information, like a short description (e.g. "this number only between 1 and 5 am") or a bool column to flag disconnected lines.

That would lead to a

SELECT ([phone1], [phone1desc], [phone2], [phone2desc], [phone3] ..

Hmmm, I see what your saying. So, I suppose the answer to my original question is "it depends" judging by the range of replies that the question solicited. It depends on what the database is actually designed to achieve in the first place and what are the consequences moving from a theoretically correct design to a design that has compromises which fly in the face of conventional theory.

My thanks go out to all of you that took the trouble to reply to the original question.

There is only one satisfying way to boot a computer.

Eddy Vluggen23-Jul-10 9:13

Eddy Vluggen

23-Jul-10 9:13

Digital Thunder wrote:
s moving from a theoretically correct design to a design that has compromises

Stick to the theoretically correct design, and make sure that you can explain why you denormalized that particular table.

Your teach has prolly given some good arguments why you should apply at least 3NF. A good reason for denormalization would be partitioning - if the table were too large, and you'd like to divide it over two databases.

IRL you'll find lots of denormalized databases, simply because there's lots of people creating databases who never heard of Codd, or decided to take a shortcut.

I are Troll Suspicious | :suss:

Euhemerus23-Jul-10 10:08

23-Jul-10 10:08

Eddy Vluggen wrote:
Your teach has prolly given some good arguments why you should apply at least 3NF.

He certainly did. Basically, we were told, without normalising to 3NF we wouldn't be able to get the database to do what we want it to do and that it would be rather error prone and that if we wanted to make any money out of database design then we had better normalise to 3NF otherwise we would be penniless! The latter is a very convincing argument.

There is only one satisfying way to boot a computer.

PIEBALDconsult23-Jul-10 7:34

PIEBALDconsult

23-Jul-10 7:34

I have learned through experience to do what your instructor describes -- things will become more complex over time. Do it correctly now.

"It's better to have it and not need it than to need it and not have it."
"A stitch in time saves nine."

At one place I worked, we needed to hold a credit card number for each account (actually, account-holders also had the option not to provide one) -- great, it (and its type; Master Card, Visa, etc.) was in the Account table. Then we had a customer who wanted to store two credit card numbers (so there would be a backup in case the other was declined Roll eyes | :rolleyes:

) -- OK, the DBAs added it to the Account table with another field to indicate which one had been charged most recently. Then we had another customer who also wanted us to support ACH transactions against bank accounts -- oh, crap -- the DBAs added yet more fields to the Account table. I left the company after writing a document explaining that I thought that all that data should be moved to a separate table before things got completely out of hand.

I suspect that in your case you want to generalize it to some sort of "contact method" table which includes telephone numbers, email addresses, icy cucumbers, etc.

Corporal Agarn23-Jul-10 8:21

Corporal Agarn

23-Jul-10 8:21

I inherited a table with over 250 columns. Many no longer used, several should have been in their own table.
But the table grew just like yours (need this one thing, well now I need this one other thing).

Building normalized to start is the best path.

Euhemerus23-Jul-10 9:17

23-Jul-10 9:17

PIEBALDconsult wrote:
I suspect that in your case you want to generalize it to some sort of "contact method" table which includes telephone numbers, email addresses, icy cucumbers, etc.

Not quite sure about the icy cucumbers Laugh | :laugh:

but yes, you've hit the nail on the head, that's exactly what it's for. I actually finished college 5 years ago, and this is the first occasion that has presented itself to put into practice what I actually learned, so I'm a bit rusty at the minute and having to rely on lecture notes, assignments and Database Systems (2nd Ed.) by Begg and Connolly, which is heavy going at times to say the least. Sigh | :sigh:

There is only one satisfying way to boot a computer.

PIEBALDconsult23-Jul-10 9:42

PIEBALDconsult

23-Jul-10 9:42

Digital Thunder wrote:
heavy going at times

Yeah, the other week I again looked up (Wikipedia and others) third-normal-form and again couldn't make sense of the discussion. I absolutely know how to use third-normal-form, but the terms that are used to describe it are non-sensical (unless there is someone there actually telling you and applying it to some data).

It's kind of like being able to read, write, and speak a language fluently but not understanding linguistics. Really, only cunning linguists need to know what the term "pluperfect" means or even that it exists. And all those hours spent diagramming sentences didn't help us speak betterishly.

Euhemerus23-Jul-10 10:02

23-Jul-10 10:02

PIEBALDconsult wrote:
unless there is someone there actually telling you and applying it to some data

I know EXACTLY what you mean. When the lecturer is explaining examples to drive home a point, it's quite obvious what he is actually getting at. However, I to have looked at a few examples on the web and some of them have only served to confuse me even more than I was! I did however, find a very good article that condensed a lot of detail into relatively few pages and the examples given really did a good job in explaining the detail.

There is only one satisfying way to boot a computer.

PIEBALDconsult23-Jul-10 10:03

PIEBALDconsult

23-Jul-10 10:03

Digital Thunder wrote:
confuse me even more

Exactly.

Digital Thunder wrote:
a very good article

Got a link?

Euhemerus23-Jul-10 10:15

Get Max Number for each department

23-Jul-10 10:15

Yes, there's this one I found very useful: http://www.phlonx.com/resources/nf3/[^] The one I was referring to is this one: http://www.databasedesign-resource.com/entity-relationship.html[^] and download the free ebook.

There is only one satisfying way to boot a computer.

SatyaKeerthi1521-Jul-10 19:49

SatyaKeerthi15

21-Jul-10 19:49

Query for Getting Max number.

For Example: I have Department and Employee

For DeptId 1: Emp No 12,16,19
and For 2: 18,21,17
For 3: 27,28,16

I have to get Max(EmpNo) For department. If I want to get Max(EmpNo) for Dept(1,2)
output will be 19,21.

How can I get this using sqlserver2005

Mycroft Holmes21-Jul-10 20:01

Mycroft Holmes

21-Jul-10 20:01

using row_number and partition

SELECT DeptId ,EmpNo 
FROM (SELECT 
   *,
   ROW_NUMBER() OVER(PARTITION BY DeptId ORDER BY EmpNo DESC)HighID FROM tableName) X
WHERE X.HighID = 1

Never underestimate the power of human stupidity
RAH

SatyaKeerthi1521-Jul-10 20:28

SatyaKeerthi15

21-Jul-10 20:28

Thank you so much. Its working fine ...

Mycroft Holmes21-Jul-10 20:56

Mycroft Holmes

21-Jul-10 20:56

See the replies from Luc and Goutam, my answer is NOT the simplest way.

Comes from applying the wrong tool for the job.

Never underestimate the power of human stupidity
RAH

Goutam Patra21-Jul-10 20:32

Goutam Patra

21-Jul-10 20:32

Why not in this simple way

SELECT DEPTID, MAX(EMPNO) FROM TABLENAME GROUP BY DEPTID