Database Design Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database Design Question

Author

Topic

inrsence
Starting Member

48 Posts

Posted - 2002-07-10 : 03:09:27

Hello all,

I'm trying to figure out what the ideal solution is to a seemingly simple setup. All comments are welcome.

I have a table storing contact information. I have set it up using separate tables to use the most appropriate sized columns.

Contacts
--------
ContactID int
ContactTypeID tinyint
ReferenceID int

Emails
-------
EmailID int
Email varchar(255)

URLs
-------
URLID int
URLTypeID tinyint
URL varchar(1000)

Phones
--------
PhoneID int
Phone varchar(25)

Now the URL and Email tables will be hit constantly to verify addresses, etc. I was thinking that since there will be hundreds of thousands and eventually millions of rows in each of these tables that it would be best to have separate indexes.

The alternative scenario is something like this:

Contacts
--------
ContactID int
ContactTypeID tinyint
Contact varchar(1000)

Although I prefer this scenario for a number of reasons, I was concerned about switching to the varchar(1000) for all rows as opposed to just in the URLs table.

In this scenario, I was figuring I could mimic the tables Emails. URLs, and Phones with views instead.

Am I going in the right direction or is there another way to approach this?

Thanks in advance,
Greg

davy_boy2000
Starting Member

28 Posts

Posted - 2002-07-10 : 05:52:57

How about having the tables on seperate filegroups, and the indexes on seperate filegroups also. All on different drives.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-10 : 07:56:14

Can you please post actual ddl, rather than this psuedo-design-code? It may be clear to you what you mean, but to me, I don't understand how these two schema would work. For example, in your first Contracts table, what is 'ReferenceID' and what does it Reference?

<O>

inrsence
Starting Member

48 Posts

Posted - 2002-07-10 : 11:13:39

Here you go:

CREATE TABLE [Contacts] (
[ContactID] [int] NOT NULL ,
[ContactTypeID] [tinyint] NULL ,
[ReferenceID] [int] NULL ,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[ContactID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Contacts_ContactTypes] FOREIGN KEY
(
[ContactTypeID]
) REFERENCES [ContactTypes] (
[ContactTypeID]
)
) ON [PRIMARY]
GO

CREATE TABLE [URLs] (
[URLID] [int] NOT NULL ,
[URL] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URLTypeID] [tinyint] NULL ,
CONSTRAINT [PK_URLs] PRIMARY KEY CLUSTERED
(
[URLID]
) ON [PRIMARY] ,
CONSTRAINT [FK_URLs_URLTypes] FOREIGN KEY
(
[URLTypeID]
) REFERENCES [URLTypes] (
[URLTypeID]
)
) ON [PRIMARY]
GO

CREATE TABLE [URLTypes] (
[URLTypeID] [tinyint] NOT NULL ,
[URLType] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IndustryID] [tinyint] NULL ,
CONSTRAINT [PK_URLTypes] PRIMARY KEY CLUSTERED
(
[URLTypeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Phones] (
[PhoneID] [int] IDENTITY (1, 1) NOT NULL ,
[Phone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Phones] PRIMARY KEY CLUSTERED
(
[PhoneID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

ReferenceID is a generic reference to the PK of one of the data tables. For example, ReferenceID changes in meaning and can point to PhoneID, URLID, or EmailID.

Hope that clarifies the matter.

Greg

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-10 : 11:23:26

So how do you map users to contacts? and how bout the poorly performing query in question...

The picture is getting clearer.

<O>

inrsence
Starting Member

48 Posts

Posted - 2002-07-10 : 12:17:29

Page,

Ha ha.. there is so much in there I can't begin to explain.

All of that is stored in another set of tables as these addresses can belong to multiple users. But that part I think I have in order as it is making use of the ContactID.

I guess my desire would be t othrow away all those separate tables and use the one unified Contacts table (that has a larger varchar column to store the actual contact information).

My question is really about how SQL will allocate disk space when using the varchar.. and will that adversely effect performance.

AS for the queries, if I wanted all phone numbers, I would write something like this:

SELECT * -- I did that for you Page ;)
FROM Contacts c INNER JOIN Phones p ON c.ReferenceID = p.PhoneID
WHERE ContactTypeID = 1

To get all contacts resoloved in one query is actually impossible without using a UNION as best I can tell since the ReferenceID is ambiguous.

Well.. hope that clears it up some more.

Thanks again,
Greg

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-10 : 12:24:26

to answer your question about how sql stores varchars . . .

you have 2 bytes to describe then length and then as many bytes as there are length...

so, lets say you have a varchar(10 called @vc1 and a char(10) called @c1 ...

if you set @vc1 and @c1 to nothing, @vc1 will take up 2 bytes and @c1 will take up 10. If you set both to a string of 'helloworld', then @vc1 will take up 12 and @c1 will take up 10. If you set both to a string of just 'hello' @vc1 will take up 7 and @c1 will take up 10 . . .

<O>

inrsence
Starting Member

48 Posts

Posted - 2002-07-10 : 13:28:10

Page,

Thanks for that explanation. Can you clarify one more thing for me though?

Doe sql allocate more space based on using varchar(1000) say instead of varchar(100)?

I understand that when you use varchar there is the additional cost of the lookup to determine the length, but since all scenarios I am playing with use them, the point is moot.

It seems though that I can actually get away with the single Contacts table. Which I would be happy to see happen!

Thanks in advance,
Greg

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-10 : 13:43:20

quote:

you have 2 bytes to describe then [sic] length and then as many bytes as there are length...

This is correct, a variable character column requires two bytes for the length of the data you store in it, and then one byte per character (or two bytes if a unicode column, a nvarchar).

SQL Server would thus allocate the same space for a varchar(100) and varchar(1000), 2 bytes, if both columns were NULL. Once you put data in the column, you follow the rule above to determine the storage required.

Jonathan Boott, MCDBA

inrsence
Starting Member

48 Posts

Posted - 2002-07-10 : 15:57:12

Excellent.. that's exactly what I wanted to know..

you guys are the best.

Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources