Re: [SQLDownUnder] Table Design

[noonie]

Sorry for the late reply,

 

I'm still a bit unsure of what an "Individual" is in your model. I'd imagine this as either a person doing business as a sole trader and not a company OR someine in the system not associated with a company.

 

Here's an example which would make the queries to populate your grids very simple. I'm sure someone else can come up with a better schema.

 

-- create the tables

create table dbo.Companies (
 CompanyID int not null,
 CompanyName varchar(50) not null,
 CompanyEmail varchar(255),
 Suburb varchar(50),
 PostCode varchar(4),
 MoreCompanyInfo varchar(500),

 CONSTRAINT PK_Companies_CompanyID
  PRIMARY KEY CLUSTERED (CompanyID),

 CONSTRAINT UC_Companies_CompanyName
  UNIQUE  NONCLUSTERED (CompanyName)
)

create table dbo.Contacts (
 ContactID int identity not null,
 ContactName varchar(50) not null,
 ContactEmail varchar(255),
 Phone varchar(50),
 MoreContactInfo varchar(500),
 CompanyID int,

 CONSTRAINT PK_Companies_ContactID
  PRIMARY KEY CLUSTERED (ContactID),

 CONSTRAINT UC_Contacts_ContactName
  UNIQUE  NONCLUSTERED (ContactName),

 CONSTRAINT FK_Contacts_Companies
  FOREIGN KEY (CompanyID)
  REFERENCES dbo.Companies (CompanyID)
)

-- add some companies

insert into dbo.Companies (CompanyID, CompanyName, CompanyEmail, Suburb, PostCode, MoreCompanyInfo)
values(0,'Company Zero', 'info@xxxxxxxxxxxx', 'North', '1234', 'This is a company that has no contacts')

insert into dbo.Companies (CompanyID, CompanyName, CompanyEmail, Suburb, PostCode, MoreCompanyInfo)
values(1,'Company One', 'info@xxxxxxxxxxxx', 'South', '1235', 'This is a company that has one contact')

insert into dbo.Companies (CompanyID, CompanyName, CompanyEmail, Suburb, PostCode, MoreCompanyInfo)
values(2,'Company Two', 'info@xxxxxxxxxxxx', 'East', '1236', 'This is a company that has two contacts')

insert into dbo.Companies (CompanyID, CompanyName, CompanyEmail, Suburb, PostCode, MoreCompanyInfo)
values(3,'Joe Blow', 'info@xxxxxxxxxxxxxx', 'West', '1237', 'This is not a company. It is an individual, not incorporated, operating as a sole trader.')

-- add some contacts

insert into dbo.Contacts (ContactName, ContactEmail, Phone, MoreContactInfo, CompanyID)
values('Joe Blow', 'joeb@xxxxxxxxxxxxxx', '02 1234 5678', 'Joe is a sole trader', 3)

insert into dbo.Contacts (ContactName, ContactEmail, Phone, MoreContactInfo, CompanyID)
values('Jane Doe', 'janed@xxxxxxxxxxxxxx', '02 1234 5678', 'Jane is Joe''s sister and helps with the business', 3)

insert into dbo.Contacts (ContactName, ContactEmail, Phone, MoreContactInfo, CompanyID)
values('Mark Black', 'mblack@xxxxxxxxxxxx', '02 5678 5678', 'Mark is in sales', 2)

insert into dbo.Contacts (ContactName, ContactEmail, Phone, MoreContactInfo, CompanyID)
values('Mary White', 'mwhite@xxxxxxxxxxxx', '03 5678 5678', 'Mary is in tech support', 2)
 
insert into dbo.Contacts (ContactName, ContactEmail, Phone, MoreContactInfo, CompanyID)
values('John Smith', 'jsmith@xxxxxxxxxxxx', '02 1234 1234', 'John is a company director', 1)

insert into dbo.Contacts (ContactName, ContactEmail, Phone, MoreContactInfo, CompanyID)
values('Bobby', 'bobby1234@xxxxxxxxxxx', '07 1234 1234', 'Bobby''s just a guy I know.', NULL)

--
Regards,
Neale NOON

 

 

 

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net