ChrisM@Work (4/26/2013)
Show me a person table which doesn’t have columns firstname, middlenames, lastname. However, not every person has or is willing to reveal their middle name(s). Some are unwilling to reveal their first name(s). It’s a really good fit for most cases though, and a few nulls are more acceptable than the cost and complexity of normalising out the separate name elements.
Actually First Name, Middle Name, Last Name, and Called Name - four columns not three - seems to be pretty column, sometimes "Other Names" instead of "Middle Name"; the Called Name is becoming common because companies are slowly discovering that calling someone by a first name which they never use will irritate them and irritating one's customers is not a good thing; Other Names becuase some people have more than one middle name. These name columns can be nullable (except Last Name) but don't need to be since a zero length string works just fine.
Few persons have a unique string as one of those name elements, so there’s a valid argument for taking this one step further and having names – perhaps all names ever written down – in a lookup with a many to many link between person and names. It might take up a little less disk space (if it included only the names in use in your persons table) but wouldn’t it be a PITA to interrogate, never mind maintain!
Anyone who wants to carry out that normalisation and isn't stupid must have a very large number of people in their database. And probably assumes that no-one ever changes a name. And probably hasn't worked out that doing that increases the risk of data entry errors unless you ensure that the GUI doesn't present a list of names to choose from. There may even be a problem in determining when two names are the same - after all, Alan and Ailean are the same name, aren't they? And Ian and Iain? And Colin and Cailean? And Alasdair and Alastair? And Elizabeth and Betty? And Jack and John?
Of course if each person has exacly 1 staff phne number, exactly one personal fixed phone number, exactly one mobile fixed phone number, and so onm - exactly one phone number in each possible category of phone numbers - then it's perfectly reasonable to put teh numbers in the persons table. Otherwise, you have to look at what the actual data is like - vast munbers of nullable col;umns are not a good idea, but a small number could be OK.
I believe this is the commonest solution, at least in my experience. Phone usage / ownership in the West and elsewhere is consistent enough that homephone, workphone and mobilephone fit most cases very well. The devices are not exactly equivalent primarily because of the attribute of usage pattern – they’re not the same as phone1, phone2 and phone3.
I don't know what people do in databases generally, but I do know that it's not extremely rare for people to have more than 3 phones. Now I only have 4 (UK home, Spain home, UK mobile, Spain mobile) but a few years back I had 8: UK Home, UK mobile, London office switchboard, London office direct line, Spain home, Spain mobile, India mobile, India office. Most people in the firm had 4 phones or more. We didn't keep the numbers in a database though.
Anyway, the classical solution is the one mentioned by Patrick McG: a table with
person_id, phone_id, connection_type (connection type being values like "staff phone number", "personal mobile", "manager's number", "secretary's number", and so on.
I’ve seen this only once in a fair few years of db work and IIRC it was barely worth the effort (of maintaining the extra table) because there were very very few edge cases where a person wished to have more than one ‘number’ in any one category. That doesn’t mean I disagree with it – back to the first paragraph, you may be working for a business where persons routinely own or use more phone numbers than the bogstandard three. But for the vast majority of cases, three covers most bases, just like three names.
I tend to agree that a three number solution, or maybe a four number solution to cater for the case where staff are required to have a work mobile but mustn't use it for non-work calls so have separate work mobile and personal mobile, will cover the vast majority of cases.
Tom