July 31, 2008 at 7:49 am
Hi all,
This is my first post here and I'm new to SQL/Databases so please bare with me...
Right, I understand that if you have a multivalued attribute like a persons phone number(s) that you should have a seperate table for the numbers with a one to many relationship between table listing the people and the table listing the numbers. Thus avoiding the 'phonenumber1', 'phonenumber2' column scenario.
My question is: If you had a compound attribute, such as an address (line one, line two etc) they would bneed to be ordered but that some would have more lines than others, would it be ok the have a table such as:
CustomerTable
([CustID Primary Key], other columns excluding adress etc)
and
CustomerAddressTable
([CustID] Foriegn key, [AddressLineNumber],[AddressLine])
(-----------------PK---------------------)
with values
CustID1, 1, 'Tom's house'
CustID1, 2, 'Tom's Street'
CustID2, 1, 'John's flat'
CustID2, 2, 'John's building'
CustID2, 3, 'John's street'
So I've got a two line address but John's got a three line address.
This would removes the classic nullable values found with having Address lines 1,2,3,4,5 in the customer table, most of which might not be used.
OR
since address line 1 is a different attribute to Address line 2, should they technically be in seperate tables? Then I'd have potentially empty tables (not really a problem from a normalisation point of view) and still no nullable values?
I hope this is clear. I will have gone over this in my head enough times for none of it to make sense before posting it.
Database design is something I'd like to be good at but am really not a natural in at all.
Anyway, please let me know if this is correct?
Thanks very much,
Tom
July 31, 2008 at 8:33 am
I'm not saying that this is the best way, but I have always had (in the US) address tables with Address_1, Address_2, Address_3, although in the US it is rare to need the 3 address lines. So a typical addresses table in my systems has been something like this:
[font="Courier New"]CREATE TABLE [dbo].[Addresses]
(
[Address_ID] [int] IDENTITY(1,1) NOT NULL,
[Address1] [nvarchar](30) NOT NULL,
[Address2] [nvarchar](30) NULL,
[City] [nvarchar](20) NOT NULL,
[State_Province_ID] [int] NOT NULL,
[Postal_Code] [varchar](10) NULL,
[Country_Code] [char](2) NOT NULL
)[/font]
I think what you are doing in your design is something like this:
[font="Courier New"]CREATE TABLE [dbo].[Addresses]
(
[Customer_ID] [int] NOT NULL,
[Line_Number] [tinyint] NOT NULL,
[Value] [nvarchar](30) NOT NULL -- this could be street, city, state/province, postal code, country, etc..
)[/font]
Which is a method I have not seen used before, but if this is the design you would not need another table for multiple address lines.
Am I correct in what you are doing?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 9:02 am
Thanks for the reply! First I should say that I'm not actually DOING anything. I'm just interested in database design.
I'm a level 2/3 techie but I've got a few projects which involve SQL (Enterprise Vault/MOM2005/DeviceLock) so I've been getting into it just to bolster my knowledge. Database design has just caught my interest.
I'm thinking in terms or normalisation. You table is good, and exactly what I would have used if I were actually creating a database, but I've seen it raised before that Post code is dependant on City, not the Table's primary key so there should (in theory) be a seperate table for cities and postcodes, linked to the address table. That would be before denormalisation, of course. And dependant on you having a (huge) list of cities and their applicable postcodes.
Is there a huge gap between the theory and what actually goes on in real life?
Many thanks for your reply.
Tom
July 31, 2008 at 10:07 am
The answer is "it depends". In reality a cities table with postal codes would be a great thing and if your product only deals with industrialized nations like the US, Canada, UK, etc.. there are companies that will provide you this data along with updates, so go for it, but if you need to deal with addresses all over the world having a postal codes table doesn't work quite as well.
I wouldn't call it a HUGE gap, the gap really depends on the knowledge of the designer and, in many cases, is the database an upgrade from a flat file system or MS Access, where in order to normalize the database you would have to make many code changes. Most do not re-factor the database when upgrading so those db's are usually denormalized.
It also depends on the main use of the system, a lot of updates and inserts you want to normalize, mostly selects, denormalizing some is usually okay.
Here is a link to a recent and interesting article about normalization, http://www.simple-talk.com/community/blogs/tony_davis/archive/2008/07/21/63094.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 1:42 pm
I know full well that the way things 'should' work and the way you make them work can be very different. I'll have a look at that article you linked to, thanks again!
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply