April 9, 2015 at 6:08 pm
Hi, I think I am closer to posing a clearer question. Here is a company information table that I split from the address one:
USE DataCompany
CREATE TABLE CompanyInformation
( CompanyCategory varchar (100)
,CompanyStatus varchar (35)
,CountryOfOrigin varchar (52)
,DissolutionDate date
,IncorporationDate date
,Accounts_AccountRefDay tinyint
,Accounts_AccountRefMonth tinyint
,Accounts_AccountsNextDueDate date
,Accounts_AccountsLastMadeUpDate date
,Accounts_AccountCategory varchar (27)
,[Returns_NextDueDate] date
,[Returns_LastMadeUpDate] date
,Mortgages_NumMortCharges smallint
,Mortgages_NumMortOutstanding smallint
,Mortgages_NumMorgPartSatisfied smallint
,Mortgages_NumMorgSatisfied smallint
,SICCode_SicText_1 varchar (120)
,SICCode_SicText_2 varchar (120)
,SICCode_SicText_3 varchar (120)
,SICCode_SicText_4 varchar (120)
,LimitedPartnerships_NumGenPartners tinyint
,LimitedPartnerships_NumLimPartners tinyint
,URI varchar (255)
,PreviousName_1CONDATE date
,PreviousName_1CompanyName varchar (255)
,PreviousName_2CONDATE date
,PreviousName_2CompanyName varchar (255)
,PreviousName_3CONDATE date
,PreviousName_3CompanyName varchar (255)
)
The address table:
CREATE TABLE MasterAddress
(
ID int IDENTITY(1,1)PRIMARY KEY
,CompanyName varchar(255)
,CompanyNumber varchar(8)
,RegAddress_CareOf varchar(100)
,RegAddress_POBox varchar (20)
,RegAddress_AddressLine1 varchar (100)
,RegAddress_AddressLine2 varchar(75)
,RegAddress_PostTown varchar (35)
,RegAddress_County varchar(30)
,RegAddress_Country varchar(52)
,RegAddress_PostCode varchar (10)
);
And the Contact Information table, of which there will be approximately 50 with different names, but all the same structure:
CREATE TABLE ContactInformation
(
ID int IDENTITY(1,1) PRIMARY KEY
,Sector varchar (60)
,InfluenceClassification varchar (25)
,FirstName varchar(35)
,LastName varchar(70)
,Title varchar (120)
,CompanyName varchar(150)
,Phone varchar (15)
,Email varchar (255)
,LinkedinID varchar (255)
,Website varchar (255)
,CompanyRevenue varchar (25)
,CompanySize varchar (25)
,Tier tinyint
)
The address table needs to be connected to the contact information tables via the companyname column. So shall I do what Eric M Russell suggested:
Eric M Russell (4/3/2015)
My understanding is that you have a Names table, and then you have an Address table. Actually you have multiple Names tables for some reason, but we'll put that aside. I'll assume all these Names tables have the same column structure. So, you need to join the Names table with the Address table on CompanyName and then update a foreign key in the Names table containing the Address ID. That is, you need to link Companies with Addresses.Below is an example of a joined update.
update Nset N.AddressID = A.AddressID
from tblNames1 as N
join Address as A on A.CompanyName = N.CompanyName;
Which steps do I need to take before I do this? and does it matter that all of the companies in the contact information tables are in the address table but not the other way round i.e. the address table has over 10 million rows and the contact information around a million? What I want to know is would this be the best way to do this or would a flat table not be a solution here?
Also, if the company information table does not share the company name column how could it be matched up in the same way so that when necessary all the data about a particular person and that company would match up? I know this is elementary stuff but I've not been using SQL server very long. Any comments would be great. Thanks:-)
April 10, 2015 at 7:20 am
tomsharp85 (4/9/2015)
Hi, I think I am closer to posing a clearer question. Here is a company information table that I split from the address one:
USE DataCompanyCREATE TABLE CompanyInformation
( CompanyCategory varchar (100)
,CompanyStatus varchar (35)
,CountryOfOrigin varchar (52)
,DissolutionDate date
,IncorporationDate date
,Accounts_AccountRefDay tinyint
,Accounts_AccountRefMonth tinyint
,Accounts_AccountsNextDueDate date
,Accounts_AccountsLastMadeUpDate date
,Accounts_AccountCategory varchar (27)
,[Returns_NextDueDate] date
,[Returns_LastMadeUpDate] date
,Mortgages_NumMortCharges smallint
,Mortgages_NumMortOutstanding smallint
,Mortgages_NumMorgPartSatisfied smallint
,Mortgages_NumMorgSatisfied smallint
,SICCode_SicText_1 varchar (120)
,SICCode_SicText_2 varchar (120)
,SICCode_SicText_3 varchar (120)
,SICCode_SicText_4 varchar (120)
,LimitedPartnerships_NumGenPartners tinyint
,LimitedPartnerships_NumLimPartners tinyint
,URI varchar (255)
,PreviousName_1CONDATE date
,PreviousName_1CompanyName varchar (255)
,PreviousName_2CONDATE date
,PreviousName_2CompanyName varchar (255)
,PreviousName_3CONDATE date
,PreviousName_3CompanyName varchar (255)
)
This table is still horribly denormalized. What are you going to do when you want to have a SicText5? Or a 4th PreviousName?
The address table:
CREATE TABLE MasterAddress(
ID int IDENTITY(1,1)PRIMARY KEY
,CompanyName varchar(255)
,CompanyNumber varchar(8)
,RegAddress_CareOf varchar(100)
,RegAddress_POBox varchar (20)
,RegAddress_AddressLine1 varchar (100)
,RegAddress_AddressLine2 varchar(75)
,RegAddress_PostTown varchar (35)
,RegAddress_County varchar(30)
,RegAddress_Country varchar(52)
,RegAddress_PostCode varchar (10)
);
The address table looks much better. I would recommend making Country a char(2) and using the ISO country codes instead of putting in the full name but otherwise this is looking better.
And the Contact Information table, of which there will be approximately 50 with different names, but all the same structure:
That is the wrong way to do that. Don't create 50 identical tables with different names. Create one table with an extra column to indicate the type. I still don't at all understand why you have company revenue and company size in the the contact table. That just makes no sense at all.
The biggest challenge you have at the moment is that you are trying to parse out csv data into table structures that don't make a lot of sense. You are fighting the database design a lot more than you realize.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2015 at 9:03 am
Hi Steve, I am not sure what you mean "What are you going to do when you want to have a SicText5? Or a 4th PreviousName?" How could I design it to stop that?...
I take your point about the number of tables, so it is perfectly ok to have one table with maybe 3 classification columns? The amount of sub sectors is very large which Is why I opted for seperate tables..
April 10, 2015 at 9:24 am
tomsharp85 (4/10/2015)
Hi Steve, I am not sure what you mean "What are you going to do when you want to have a SicText5? Or a 4th PreviousName?" How could I design it to stop that?...I take your point about the number of tables, so it is perfectly ok to have one table with maybe 3 classification columns? The amount of sub sectors is very large which Is why I opted for seperate tables..
Sure no reason you can't have 3 classification columns. But don't make them clasification1, classification2.
My point about your tables is they aren't normalized. You have SicText1, SicText2, SicText3... That should be a separate table.
create table SICCode
(
SicText varchar(120)
)
That way if you have 2, you only have 2 rows. If you want a 5th or an 8th you just add rows.
Same thing with many of your other tables.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply