Database level lock: following

Showing posts with label following. Show all posts

Thursday, March 29, 2012

Database in loading state

I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advance
Forgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance
|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance
sql

Database in loading state

I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advanceForgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:
> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:
> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases that
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to the
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance

Database in loading state

I have several databases in a loading state, I have tried using the detach
option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
SQLState: 42000)
Error 15010: The database 'name' does not exist. Use sp_helpdb to show
available databases.
When using the sp_helpdb command, of course it only shows the databases that
are not in the loading state.
I need to get the databases that in loading state, detached or removed
completely. My nightly backups are failing since they can not attach to the
databases (loading)
All assistance in this matter is greatly appreciated and thanks in advanceForgot to advise. Using SQL 2000 with SP 3 on Win2K server
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases th
at
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to t
he
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance|||Never mind... I found the solution... Forgot that I needed to use the
RECOVERY DATABASE WITH RECOVERY command
"scuba79" wrote:

> I have several databases in a loading state, I have tried using the detach
> option (via EM) but I get the following error: Microsoft SQL-DMO (ODBC
> SQLState: 42000)
> Error 15010: The database 'name' does not exist. Use sp_helpdb to show
> available databases.
> When using the sp_helpdb command, of course it only shows the databases th
at
> are not in the loading state.
> I need to get the databases that in loading state, detached or removed
> completely. My nightly backups are failing since they can not attach to t
he
> databases (loading)
> All assistance in this matter is greatly appreciated and thanks in advance

Thursday, March 22, 2012

Database export from Vwd 2005

I recently posted this question to MSDN forum and was directed to re-ask it on this forum. Following are the question and answer I got...

>>>>>>>>>>

I am trying to export a table from VWD into a CSV, MySQL or text file so I can transmit it to my hosting manager. They won't take it in as a .mdf - After browsing around on VWD Options & Help I can't find someting that specifically has export options for database.

Anyone ?

Answer :

you maybe best asking the gurus over at the ASP.NET forums:

http://forums.asp.net

I think this would come from the database manager itself on options to export data, not within VWD however. Again, best to ask there

I am looking for the same thing--please let me know when/if you find out how.

Wednesday, March 7, 2012

Database design question - Using Field definition table

Hi,
We have a table similar to the following schema.
Product table
product_id int,
product_name varchar(50),
product_desc varchar(100),
product_price money,
product_sub_category_id int
We also have tables for product category and sub category. We would
like to save audit trail information whenever a new product is added,
changed name, description or price, and moved to another sub category
or category. We need to save modified user, modification datetime and
some other audit info as well. It seems there are two options available
saving this data i.e. either using a large table that has all audit
columns or using field definition table. The field definition table has
a field id and audit field column and the audit table will have
product_id, modified field id, new value, old value, modified user and
modification date.
My question is which design is efficient? It seems the first is pretty
straight forward and easy to implement, but stores redundant
information and grows quickly. The second solution seems to reduce
redundancy, but end up saving multiple rows if there are multiple
updates at one time. Also, we need to make join to the same table when
we write complex queries.
Any comments or suggestions would be appreciated.
Thanks,It sounds like you already understand the tradeoffs pretty well, at
least between the alternatives given.
Not knowing the details of your operation makes it hard to give
meaningful advice. Just looking at the given information, I have to
suspect that the most common change would be to product_price. It
also makes me wonder if the price does not belong in a price table
instead, with effective dates and who-changed-it-when information. In
that configuration the price table itself becomes its own audit table.
If changes to the rest of the columns are infrequent enough then that
would allow the simple approach of duplicating the entire row for the
other columns. When space permits I much prefer the simple approach,
as it makes using the audit data so much simpler.
Roy Harvey
Beacon Falls, CT
On 5 Jul 2006 16:12:54 -0700, sreedhardasi@.gmail.com wrote:
>Hi,
>We have a table similar to the following schema.
>Product table
> product_id int,
> product_name varchar(50),
> product_desc varchar(100),
> product_price money,
> product_sub_category_id int
>We also have tables for product category and sub category. We would
>like to save audit trail information whenever a new product is added,
>changed name, description or price, and moved to another sub category
>or category. We need to save modified user, modification datetime and
>some other audit info as well. It seems there are two options available
>saving this data i.e. either using a large table that has all audit
>columns or using field definition table. The field definition table has
>a field id and audit field column and the audit table will have
>product_id, modified field id, new value, old value, modified user and
>modification date.
>My question is which design is efficient? It seems the first is pretty
>straight forward and easy to implement, but stores redundant
>information and grows quickly. The second solution seems to reduce
>redundancy, but end up saving multiple rows if there are multiple
>updates at one time. Also, we need to make join to the same table when
>we write complex queries.
>Any comments or suggestions would be appreciated.
>Thanks,|||Hi
I agree with Roy , you will be benefit from having price's table . Much
easier to audit instead of having triggers or something else to track the
info
<sreedhardasi@.gmail.com> wrote in message
news:1152141174.756626.230500@.v61g2000cwv.googlegroups.com...
> Hi,
> We have a table similar to the following schema.
> Product table
> product_id int,
> product_name varchar(50),
> product_desc varchar(100),
> product_price money,
> product_sub_category_id int
> We also have tables for product category and sub category. We would
> like to save audit trail information whenever a new product is added,
> changed name, description or price, and moved to another sub category
> or category. We need to save modified user, modification datetime and
> some other audit info as well. It seems there are two options available
> saving this data i.e. either using a large table that has all audit
> columns or using field definition table. The field definition table has
> a field id and audit field column and the audit table will have
> product_id, modified field id, new value, old value, modified user and
> modification date.
> My question is which design is efficient? It seems the first is pretty
> straight forward and easy to implement, but stores redundant
> information and grows quickly. The second solution seems to reduce
> redundancy, but end up saving multiple rows if there are multiple
> updates at one time. Also, we need to make join to the same table when
> we write complex queries.
> Any comments or suggestions would be appreciated.
> Thanks,
>|||Thanks a lot for your suggestions. I think it is a good idea to have a
price table.
Uri Dimant wrote:
> Hi
> I agree with Roy , you will be benefit from having price's table . Much
> easier to audit instead of having triggers or something else to track the
> info
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1152141174.756626.230500@.v61g2000cwv.googlegroups.com...
> > Hi,
> >
> > We have a table similar to the following schema.
> >
> > Product table
> > product_id int,
> > product_name varchar(50),
> > product_desc varchar(100),
> > product_price money,
> > product_sub_category_id int
> >
> > We also have tables for product category and sub category. We would
> > like to save audit trail information whenever a new product is added,
> > changed name, description or price, and moved to another sub category
> > or category. We need to save modified user, modification datetime and
> > some other audit info as well. It seems there are two options available
> > saving this data i.e. either using a large table that has all audit
> > columns or using field definition table. The field definition table has
> > a field id and audit field column and the audit table will have
> > product_id, modified field id, new value, old value, modified user and
> > modification date.
> >
> > My question is which design is efficient? It seems the first is pretty
> > straight forward and easy to implement, but stores redundant
> > information and grows quickly. The second solution seems to reduce
> > redundancy, but end up saving multiple rows if there are multiple
> > updates at one time. Also, we need to make join to the same table when
> > we write complex queries.
> >
> > Any comments or suggestions would be appreciated.
> >
> > Thanks,
> >|||Thanks a lot for your suggestions. I think it is a good idea to have a
price table.
Uri Dimant wrote:
> Hi
> I agree with Roy , you will be benefit from having price's table . Much
> easier to audit instead of having triggers or something else to track the
> info
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1152141174.756626.230500@.v61g2000cwv.googlegroups.com...
> > Hi,
> >
> > We have a table similar to the following schema.
> >
> > Product table
> > product_id int,
> > product_name varchar(50),
> > product_desc varchar(100),
> > product_price money,
> > product_sub_category_id int
> >
> > We also have tables for product category and sub category. We would
> > like to save audit trail information whenever a new product is added,
> > changed name, description or price, and moved to another sub category
> > or category. We need to save modified user, modification datetime and
> > some other audit info as well. It seems there are two options available
> > saving this data i.e. either using a large table that has all audit
> > columns or using field definition table. The field definition table has
> > a field id and audit field column and the audit table will have
> > product_id, modified field id, new value, old value, modified user and
> > modification date.
> >
> > My question is which design is efficient? It seems the first is pretty
> > straight forward and easy to implement, but stores redundant
> > information and grows quickly. The second solution seems to reduce
> > redundancy, but end up saving multiple rows if there are multiple
> > updates at one time. Also, we need to make join to the same table when
> > we write complex queries.
> >
> > Any comments or suggestions would be appreciated.
> >
> > Thanks,
> >

Saturday, February 25, 2012

Database Design Question

Which of the following options is best?

Option A)

One database with all the tables for all applications (HR, TechStaff, Inventory, Purchasing, etc.). The bonus to this is all the data is stored in one place and the data is not duplicated, but when the database has to come down, all applications will be unavailable. Plus, volume may be present an read/write row/column issue.

Option B)

Multiple databases... one for each application. Then to replicate the necessary tables to each database as needed. For example, replicate the employee table (ID, firstname, lastname) to the databases that would require that data. The replication would be setup to be one-way replication. Bonus is that if one database goes down, the others are still available, but a downside is the same data is stored in multiple places and updated through replication (which could slow down the server).

Thoughts?

Seems like you are using 'database' to refer to an 'instance' of SQL Server? If your primary concern is high-availability, you may want to consider cluster or mirroring or even log shipping.

Thanks

|||

I would go with B option. It proved to be more flexiblein my case. I used a database for each application. The employees were stored in a separate database that was populated from Active Directory. Each application had an employee table that contained only the authorized employees for that specific application.

|||

Sunil Agarwal wrote:

Seems like you are using 'database' to refer to an 'instance' of SQL Server? If your primary concern is high-availability, you may want to consider cluster or mirroring or even log shipping.
Thanks

Seperate databases vs a single database on one instance os SQL Server.|||

Cristian Lefter wrote:

I would go with B option. It proved to be more flexiblein my case. I used a database for each application. The employees were stored in a separate database that was populated from Active Directory. Each application had an employee table that contained only the authorized employees for that specific application.

That is what I was doing now... but instead of maintaining multiple employee tables across many databases, I would setup the server to automatically replicate the employee tables from the main employee databases to cascade the changes down to all the other databases the required some sort of employee data.|||Both have their benefits but replication does seem to be a bit of an overhead in this case.

There is an alternative and that is to create individual databases for each application. Then, rather than replicate the data, you can create a view in the source database selecting the data you need to display in new database (eg EmployeeList - a list of employees in a HR database). This can join several table together as you would in any normal view. In the new database you create a new view which contains a simple SQL statement of "SELECT * FROM HrDbName.EmployeeList" and call it anything you like. You can then use this view as you would any other table or view. Provided your users have access to the data in both databases then it will appear as though the data resides in the same database.

This does not stop the problem of one database being unavailable affecting the others but it should result in a number of smaller databases targeted at one objective while still enabling you to effectively share the data between applications.

If the databases are on a different server then you can include the server name in the SELECT statement but you will also have to set up security between the servers.

This is a technique I have used for externally provided applications where we write our own queries. By setting up an enquiry database with views direct to the tables in the suppliers database I can create as many views as i need without any dabger of breaking the application now or in the future.

Database Design Question

What are the pros and cons of the following two design methods ?
(1) Using foreign keys to form a composite primary key of a child tables -- as in Example.
(2) Using a new key to form a single primary key of a table, and placing parent tables as only foreign keys -- as in Example 2.
Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many
-- *** Example 1 COMPOSITE FOREIGN KEY Code ***
CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangNamevarchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureIdint identity(1,1) not null,
LanguageIdint not null,
Titlevarchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE TABLE tbHeading
(
HeadingIdint identity(1,1) not null,
HeadingTextvarchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureIdint not null,
LanguageIdint not null,
HeadingIdint not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE TABLE tbParagraph
(
BrochureIdint not null,
LanguageIdint not null,
HeadingIdint not null,
SequenceNoint not null,
ParagraphTextvarchar(4000) not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
-- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***
CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangNamevarchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureIdint identity(1,1) not null,
LanguageIdint not null,
Titlevarchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure (LanguageId)
go
CREATE TABLE tbHeading
(
HeadingIdint identity(1,1) not null,
HeadingTextvarchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureHeadingMapId int identity(1,1) not null,
BrochureIdint not null,
HeadingIdint not null,
PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
FOREIGN KEY (BrochureId)
REFERENCES tbBrochure (BrochureId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON tbBrochureHeadingMap (BrochureId)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON tbBrochureHeadingMap (HeadingId)
go
CREATE TABLE tbParagraph
(
ParagraphIdint identity(1,1) not null,
HeadingIdint not null,
SequenceNoint not null,
ParagraphTextvarchar(4000) not null,
PRIMARY KEY CLUSTERED (ParagraphId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON tbBrochureHeadingMap (HeadingId)
go
It has been argued that Example 1: COMPOSITE FOREIGN KEY has the following pros, over Example 2:
1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of Nine (9) in Example 2.
2) Queries can be created with fewer joins.
For example: (one join in Example 1)
SELECT b.Title,
p.ParagraphText
FROM tbBrochure b
INNER JOIN tbParagraph p
ON (
b.BrochureId = p.BrochureId and
b.LanguageId = p.LanguageId
)
Instead Of: (two joins in Example 2)
SELECT b.Title,
p.ParagraphText
FROM tbBrochure b
INNER JOIN tbBrochureHeadingMap bhm
ON bhm.BrochureId = b.BrochureId
INNER JOIN tbParagraph p
ON p.HeadingId = bhm.HeadingId
Can anyone see any advantages of using the Example 2 over using Example 1 method ?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
<sorengi@.-NOSPAM-yahoo.com> wrote in message
news:OepiUCROEHA.3028@.TK2MSFTNGP11.phx.gbl...
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.
>
This is one of my favorite designs. It does a couple of things for you
quite nicely.
First off it gives you a single, highly efficient access path to the child
rows, and simultaneously supports your foregn key with a clustered index.
This is especially effective for modeling parent/child relationships where
the child rows will usually be accessed through the parent row. And
especially ineffective elsewhere.
Remember you will need a supporting index on the foregn key column in any
case, and if you make the foreign key the leading columns in the primary
key, you may need a secondary index on the identity column.
.. . .

>Example 1 COMPOSITE FOREIGN KEY Code ***
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go

> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
If the foreign key does not lead the Primary Key, you need a secondary index
to support the foreign key.
create index ix_brocure_lang on tbBrocure(LanguageId)
This is incredibly important for queries like
select * from tbBrocure where LanguageId = 123
or
delete tbLanguage where LanguageId = 123
Having LanguageID as the second column in the primary key just doesn't help.
And so Example 1, as written, is pretty useless. You could have left
LanguageID out of the primary key altogher.
Example 1 should be
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(LanguageId,BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
Then the foregn key is supported by an index, but you may need a secondary
index on BrocureId to support queries like
select * form tbBrochure where BrochureId = 1234
David
David
|||What you call a "single primary key" is known as a "surrogate key". I think
this article from ASPFAQ covers both sides of the debate fairly well:
http://www.aspfaq.com/show.asp?id=2504
<sorengi@.-NOSPAM-yahoo.com> wrote in message
news:OepiUCROEHA.3028@.TK2MSFTNGP11.phx.gbl...
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.
> (2) Using a new key to form a single primary key of a table, and placing
parent tables as only foreign keys -- as in Example 2.
>
> Relationships:
> Language to Brochure = one-to-many
> Brochure to Heading = many-to-many
> Heading to Paragraph = one-to-many
> -- *** Example 1 COMPOSITE FOREIGN KEY Code ***
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
> go
> CREATE TABLE tbHeading
> (
> HeadingId int identity(1,1) not null,
> HeadingText varchar(1000) not null,
> PRIMARY KEY CLUSTERED (HeadingId)
> )
> go
> CREATE TABLE tbBrochureHeadingMap
> (
> BrochureId int not null,
> LanguageId int not null,
> HeadingId int not null,
> PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
> FOREIGN KEY (BrochureId,LanguageId)
> REFERENCES tbBrochure (BrochureId,LanguageId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE TABLE tbParagraph
> (
> BrochureId int not null,
> LanguageId int not null,
> HeadingId int not null,
> SequenceNo int not null,
> ParagraphText varchar(4000) not null,
> PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
> FOREIGN KEY (BrochureId,LanguageId)
> REFERENCES tbBrochure (BrochureId,LanguageId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
>
> -- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***
>
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure
(LanguageId)
> go
> CREATE TABLE tbHeading
> (
> HeadingId int identity(1,1) not null,
> HeadingText varchar(1000) not null,
> PRIMARY KEY CLUSTERED (HeadingId)
> )
> go
> CREATE TABLE tbBrochureHeadingMap
> (
> BrochureHeadingMapId int identity(1,1) not null,
> BrochureId int not null,
> HeadingId int not null,
> PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
> FOREIGN KEY (BrochureId)
> REFERENCES tbBrochure (BrochureId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON
tbBrochureHeadingMap (BrochureId)
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON
tbBrochureHeadingMap (HeadingId)
> go
> CREATE TABLE tbParagraph
> (
> ParagraphId int identity(1,1) not null,
> HeadingId int not null,
> SequenceNo int not null,
> ParagraphText varchar(4000) not null,
> PRIMARY KEY CLUSTERED (ParagraphId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON
tbBrochureHeadingMap (HeadingId)
> go
>
>
> It has been argued that Example 1: COMPOSITE FOREIGN KEY has the following
pros, over Example 2:
> 1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of Nine
(9) in Example 2.
> 2) Queries can be created with fewer joins.
> For example: (one join in Example 1)
> SELECT b.Title,
> p.ParagraphText
> FROM tbBrochure b
> INNER JOIN tbParagraph p
> ON (
> b.BrochureId = p.BrochureId and
> b.LanguageId = p.LanguageId
> )
> Instead Of: (two joins in Example 2)
> SELECT b.Title,
> p.ParagraphText
> FROM tbBrochure b
> INNER JOIN tbBrochureHeadingMap bhm
> ON bhm.BrochureId = b.BrochureId
> INNER JOIN tbParagraph p
> ON p.HeadingId = bhm.HeadingId
> Can anyone see any advantages of using the Example 2 over using Example 1
method ?
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

database design question

Hi,
I've got the following problem. I've got 2-3 tables where I am keeping
information about a particular entity. Say tables A, B, C. Table A contains
the most important details about the entity, like id, name, address etc.
Tables B, C contain the id and supplementary information. I think I would
like to have at least the name on all 3 tables, ok maybe 2 of them, as the
name is very important and don't want to keep joining with the main table A
to find the name.
I do know this is not perfect practise in terms of database design. What's
the best way to maintain the names on tables B, C. A Trigger is the first
thing that springs to mind. Or are you against maintaining the names in 2-3
tables and I should instead stick to one table as my instict says!! What do
you think? Thank you.
Panos.Panos
Can you post the definition of the tables and relationship between them?
And can you elaborate a little bit what are you trying to achive?
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:8F222DF4-AB7E-425E-AE8C-BF5F7D25C55E@.microsoft.com...
> Hi,
> I've got the following problem. I've got 2-3 tables where I am keeping
> information about a particular entity. Say tables A, B, C. Table A
> contains
> the most important details about the entity, like id, name, address etc.
> Tables B, C contain the id and supplementary information. I think I would
> like to have at least the name on all 3 tables, ok maybe 2 of them, as the
> name is very important and don't want to keep joining with the main table
> A
> to find the name.
> I do know this is not perfect practise in terms of database design. What's
> the best way to maintain the names on tables B, C. A Trigger is the first
> thing that springs to mind. Or are you against maintaining the names in
> 2-3
> tables and I should instead stick to one table as my instict says!! What
> do
> you think? Thank you.
> Panos.|||If tableA is clustered indexed on ID, then you are better off than keeping
the name in just table A and joining (performance wise) than using triggers
or any other option to maintain the data integrity.
Hope this helps.
--
"Panos Stavroulis." wrote:

> Hi,
> I've got the following problem. I've got 2-3 tables where I am keeping
> information about a particular entity. Say tables A, B, C. Table A contain
s
> the most important details about the entity, like id, name, address etc.
> Tables B, C contain the id and supplementary information. I think I would
> like to have at least the name on all 3 tables, ok maybe 2 of them, as the
> name is very important and don't want to keep joining with the main table
A
> to find the name.
> I do know this is not perfect practise in terms of database design. What's
> the best way to maintain the names on tables B, C. A Trigger is the first
> thing that springs to mind. Or are you against maintaining the names in 2-
3
> tables and I should instead stick to one table as my instict says!! What d
o
> you think? Thank you.
> Panos.|||Hi,
Table A
^^^^^
col_id
entity_name
entity_type_id
short_name
alternative_name
source_unique_id
ext_ref_type_id
country_code
Source_id
record_date
Valid_from
Valid_to
event
next
prev
is_deleted
Delete_Reason
Table B
^^^^^^
col_id
prosp_name
entity_name
pairred
ref_entity_red
is_preferred
notes
jurisdiction
Entity_key
entity_type
depth
entity_form
industry_sector
industry_group
industry_subgroup
is_interesting
Yes the there is a clustered index on the col_id to answer your question. I
don't think it's too bad to join, it's just convenient to keep another name.
I think probably best if I don't maintain this duplication, I didn't like it
in the first place and have only one name on the main table A. Any other
opinions? Thanks.
Panos.
"Uri Dimant" wrote:

> Panos
> Can you post the definition of the tables and relationship between them?
> And can you elaborate a little bit what are you trying to achive?
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:8F222DF4-AB7E-425E-AE8C-BF5F7D25C55E@.microsoft.com...
>
>|||Panos
If I understood you properly you need something like that
SELECT A.entity_name,B.entity_name FROM TableA A JOIN TableB B
ON A.colid=B.colid
"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:204DBCBB-6131-4A6D-B594-3AFFE1733A51@.microsoft.com...
> Hi,
> Table A
> ^^^^^
> col_id
> entity_name
> entity_type_id
> short_name
> alternative_name
> source_unique_id
> ext_ref_type_id
> country_code
> Source_id
> record_date
> Valid_from
> Valid_to
> event
> next
> prev
> is_deleted
> Delete_Reason
> Table B
> ^^^^^^
> col_id
> prosp_name
> entity_name
> pairred
> ref_entity_red
> is_preferred
> notes
> jurisdiction
> Entity_key
> entity_type
> depth
> entity_form
> industry_sector
> industry_group
> industry_subgroup
> is_interesting
> Yes the there is a clustered index on the col_id to answer your question.
> I
> don't think it's too bad to join, it's just convenient to keep another
> name.
> I think probably best if I don't maintain this duplication, I didn't like
> it
> in the first place and have only one name on the main table A. Any other
> opinions? Thanks.
> Panos.
> "Uri Dimant" wrote:
>|||Well I don't think my problem was how to link 2 tables together! That was
more of a design issue. Anyway, I think I've made up my mind what's the best
strategy.
"Uri Dimant" wrote:

> Panos
> If I understood you properly you need something like that
> SELECT A.entity_name,B.entity_name FROM TableA A JOIN TableB B
> ON A.colid=B.colid
>
> "Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
> message news:204DBCBB-6131-4A6D-B594-3AFFE1733A51@.microsoft.com...
>
>|||Well ,design issue?
Take a look at
http://www.databaseanswers.com/data_models/index.htm -- examples
database design

"Panos Stavroulis." <PanosStavroulis@.discussions.microsoft.com> wrote in
message news:F38B0F4E-2A79-4948-B695-68D3F2E0D63C@.microsoft.com...
> Well I don't think my problem was how to link 2 tables together! That was
> more of a design issue. Anyway, I think I've made up my mind what's the
> best
> strategy.
> "Uri Dimant" wrote:
>|||Panos,
instead of denormalization, consider using indexed views and/or
covering indexes. You will get about the same performance for selects,
and you won't need to worry about data integrity.

Database Design Question

What are the pros and cons of the following two design methods ?

(1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.

POOR MAN'S ERD PROVIDED FOR SUMMARY OVERVIEW (Code provided below)

*** Example 1 COMPOSITE FOREIGN KEY ***
PK = Primary Key
FK = Foreign Key

Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many

tbLanguage tbBrochure
------- -------
- LanguageId (PK) - --\ - BrochureId (PK) -
- LangName - \--> - LanguageId (PK)(FK)-
------- / - Title -
/ / -------
/ /
/ /
tbBrochureHeadingMap / / tbHeading
------- <--/ / -------
- BrochureId (PK)(FK)- <--/ -- - HeadingId (PK) -
- LanguageId (PK)(FK)- / - HeadingText -
- HeadingId (PK)(FK)- <---/ -------
------- |
/
tbParagraph /
------- /
- BrochureId (PK)(FK)- <----/
- LanguageId (PK)(FK)-
- HeadingId (PK)(FK)-
- SequenceNo (PK) -
- ParagraphText -
-------

(2) Using a new key to form a single primary key of a table, and
placing parent tables as only foreign keys -- as in Example 2.

*** Example 2 SINGLE PRIMARY KEY ***
Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many

tbLanguage tbBrochure
------- -------
- LanguageId (PK) - --\ - BrochureId (PK) -
- LangName - \--> - LanguageId (FK) -
------- - Title -
-------
|
|
tbBrochureHeadingMap | tbHeading
--------- / -------
- BrochureHeadingMapId (PK)- / - HeadingId (PK) -
- BrochureId (FK) - <--/ / - HeadingText -
- HeadingId (FK) - <---/ -------
--------- |
|
tbParagraph /
------- /
- ParagraphId(PK) - /
- HeadingId (FK) - <-----/
- SequenceNo -
- ParagraphText -
-------

It has been argued that Example 1: COMPOSITE FOREIGN KEY has the
following pros, over Example 2:

1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of
Nine (9) in Example 2.

2) Queries can be created with fewer joins.

For example: (one join in Example 1)

SELECT b.Title,
p.ParagraphText

FROM tbBrochure b
INNER JOIN tbParagraph p
ON (
b.BrochureId = p.BrochureId and
b.LanguageId = p.LanguageId
)

Instead Of: (two joins in Example 2)

SELECT b.Title,
p.ParagraphText

FROM tbBrochure b
INNER JOIN tbBrochureHeadingMap bhm
ON bhm.BrochureId = b.BrochureId
INNER JOIN tbParagraph p
ON p.HeadingId = bhm.HeadingId

Can anyone see any advantages of using the Example 2 over using
Example 1 method ?

-- *** Example 1 COMPOSITE FOREIGN KEY Code (SQL Server 2000) ***

if exists (select * from dbo.sysobjects where id =
object_id(N'[tbParagraph]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [tbParagraph]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochureHeadingMap]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [tbBrochureHeadingMap]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbBrochure]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbBrochure]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbLanguage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbLanguage]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[tbHeading]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tbHeading]
GO

CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangNamevarchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureIdint identity(1,1) not null,
LanguageIdint not null,
Titlevarchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE TABLE tbHeading
(
HeadingIdint identity(1,1) not null,
HeadingTextvarchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureIdint not null,
LanguageIdint not null,
HeadingIdint not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE TABLE tbParagraph
(
BrochureIdint not null,
LanguageIdint not null,
HeadingIdint not null,
SequenceNoint not null,
ParagraphTextvarchar(4000) not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go

-- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***

CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangNamevarchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureIdint identity(1,1) not null,
LanguageIdint not null,
Titlevarchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure
(LanguageId)
go
CREATE TABLE tbHeading
(
HeadingIdint identity(1,1) not null,
HeadingTextvarchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureHeadingMapId int identity(1,1) not null,
BrochureIdint not null,
HeadingIdint not null,
PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
FOREIGN KEY (BrochureId)
REFERENCES tbBrochure (BrochureId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON
tbBrochureHeadingMap (BrochureId)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON
tbBrochureHeadingMap (HeadingId)
go
CREATE TABLE tbParagraph
(
ParagraphIdint identity(1,1) not null,
HeadingIdint not null,
SequenceNoint not null,
ParagraphTextvarchar(4000) not null,
PRIMARY KEY CLUSTERED (ParagraphId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON
tbBrochureHeadingMap (HeadingId)
go[posted and mailed, please reply in news]

Michael D (sorengi@.yahoo.com) writes:
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
> tables -- as in Example.
>...
> (2) Using a new key to form a single primary key of a table, and
> placing parent tables as only foreign keys -- as in Example 2.

#1 wins, hands down.

If you get very many columns in your key, it may be tempting to
introduce a artificial key.

I had one case in our data base where a table with a four-column key
needed a subtable, with two more columns in the key. I though a six-
column key was a bit too much, so I added an artificial key, and used
that in the subtable. Thus I had:

CREATE TABLE summary (id int NOT NULL,
fk_a int NOT NULL,
fk_b int NOT NULL,
fk_c int NOT NULL,
fk_d int NOT NULL,
value float NOT NULL,
CONSTRAINT pk_sum PRIMARY KEY(id),
CONSTRAINT u_sum UNIQUE(fk_a, fk_b, fk_c, fk_d))
go
CREATE TABLE details (id int NOT NULL,
fk_e int NOT NULL,
flag char(1) NOT NULL,
value float NOT NULL,
CONSTRAINT pk_details PRIMARY KEY (id, fk_e, flag),
CONSTRAINT fk_details FOREIGN KEY (id) REFERENCES parent(id))

Then much later on, I had reason to write queries against these tables,
including updates where selection was on fk_a. It was extremely messy.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Database Design Question

What are the pros and cons of the following two design methods ?
(1) Using foreign keys to form a composite primary key of a child tables -- as in Example.
(2) Using a new key to form a single primary key of a table, and placing parent tables as only foreign keys -- as in Example 2.
Relationships:
Language to Brochure = one-to-many
Brochure to Heading = many-to-many
Heading to Paragraph = one-to-many
-- *** Example 1 COMPOSITE FOREIGN KEY Code ***
CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangName varchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE TABLE tbHeading
(
HeadingId int identity(1,1) not null,
HeadingText varchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureId int not null,
LanguageId int not null,
HeadingId int not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE TABLE tbParagraph
(
BrochureId int not null,
LanguageId int not null,
HeadingId int not null,
SequenceNo int not null,
ParagraphText varchar(4000) not null,
PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
FOREIGN KEY (BrochureId,LanguageId)
REFERENCES tbBrochure (BrochureId,LanguageId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
-- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***
CREATE TABLE tbLanguage
(
LanguageId int identity(1,1) not null,
LangName varchar(255) not null,
PRIMARY KEY CLUSTERED (LanguageId)
)
go
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure (LanguageId)
go
CREATE TABLE tbHeading
(
HeadingId int identity(1,1) not null,
HeadingText varchar(1000) not null,
PRIMARY KEY CLUSTERED (HeadingId)
)
go
CREATE TABLE tbBrochureHeadingMap
(
BrochureHeadingMapId int identity(1,1) not null,
BrochureId int not null,
HeadingId int not null,
PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
FOREIGN KEY (BrochureId)
REFERENCES tbBrochure (BrochureId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON tbBrochureHeadingMap (BrochureId)
go
CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON tbBrochureHeadingMap (HeadingId)
go
CREATE TABLE tbParagraph
(
ParagraphId int identity(1,1) not null,
HeadingId int not null,
SequenceNo int not null,
ParagraphText varchar(4000) not null,
PRIMARY KEY CLUSTERED (ParagraphId),
FOREIGN KEY (HeadingId)
REFERENCES tbHeading (HeadingId)
)
go
CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON tbBrochureHeadingMap (HeadingId)
go
It has been argued that Example 1: COMPOSITE FOREIGN KEY has the following pros, over Example 2:
1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of Nine (9) in Example 2.
2) Queries can be created with fewer joins.
For example: (one join in Example 1)
SELECT b.Title,
p.ParagraphText
FROM tbBrochure b
INNER JOIN tbParagraph p
ON (
b.BrochureId = p.BrochureId and
b.LanguageId = p.LanguageId
)
Instead Of: (two joins in Example 2)
SELECT b.Title,
p.ParagraphText
FROM tbBrochure b
INNER JOIN tbBrochureHeadingMap bhm
ON bhm.BrochureId = b.BrochureId
INNER JOIN tbParagraph p
ON p.HeadingId = bhm.HeadingId
Can anyone see any advantages of using the Example 2 over using Example 1 method ?
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.<sorengi@.-NOSPAM-yahoo.com> wrote in message
news:OepiUCROEHA.3028@.TK2MSFTNGP11.phx.gbl...
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.
>
This is one of my favorite designs. It does a couple of things for you
quite nicely.
First off it gives you a single, highly efficient access path to the child
rows, and simultaneously supports your foregn key with a clustered index.
This is especially effective for modeling parent/child relationships where
the child rows will usually be accessed through the parent row. And
especially ineffective elsewhere.
Remember you will need a supporting index on the foregn key column in any
case, and if you make the foreign key the leading columns in the primary
key, you may need a secondary index on the identity column.
. . .
>Example 1 COMPOSITE FOREIGN KEY Code ***
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
If the foreign key does not lead the Primary Key, you need a secondary index
to support the foreign key.
create index ix_brocure_lang on tbBrocure(LanguageId)
This is incredibly important for queries like
select * from tbBrocure where LanguageId = 123
or
delete tbLanguage where LanguageId = 123
Having LanguageID as the second column in the primary key just doesn't help.
And so Example 1, as written, is pretty useless. You could have left
LanguageID out of the primary key altogher.
Example 1 should be
CREATE TABLE tbBrochure
(
BrochureId int identity(1,1) not null,
LanguageId int not null,
Title varchar(255) not null
PRIMARY KEY CLUSTERED(LanguageId,BrochureId),
FOREIGN KEY (LanguageId)
REFERENCES tbLanguage(LanguageId)
)
Then the foregn key is supported by an index, but you may need a secondary
index on BrocureId to support queries like
select * form tbBrochure where BrochureId = 1234
David
David|||What you call a "single primary key" is known as a "surrogate key". I think
this article from ASPFAQ covers both sides of the debate fairly well:
http://www.aspfaq.com/show.asp?id=2504
<sorengi@.-NOSPAM-yahoo.com> wrote in message
news:OepiUCROEHA.3028@.TK2MSFTNGP11.phx.gbl...
> What are the pros and cons of the following two design methods ?
> (1) Using foreign keys to form a composite primary key of a child
tables -- as in Example.
> (2) Using a new key to form a single primary key of a table, and placing
parent tables as only foreign keys -- as in Example 2.
>
> Relationships:
> Language to Brochure = one-to-many
> Brochure to Heading = many-to-many
> Heading to Paragraph = one-to-many
> -- *** Example 1 COMPOSITE FOREIGN KEY Code ***
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId,LanguageId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
> go
> CREATE TABLE tbHeading
> (
> HeadingId int identity(1,1) not null,
> HeadingText varchar(1000) not null,
> PRIMARY KEY CLUSTERED (HeadingId)
> )
> go
> CREATE TABLE tbBrochureHeadingMap
> (
> BrochureId int not null,
> LanguageId int not null,
> HeadingId int not null,
> PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId),
> FOREIGN KEY (BrochureId,LanguageId)
> REFERENCES tbBrochure (BrochureId,LanguageId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE TABLE tbParagraph
> (
> BrochureId int not null,
> LanguageId int not null,
> HeadingId int not null,
> SequenceNo int not null,
> ParagraphText varchar(4000) not null,
> PRIMARY KEY CLUSTERED (BrochureId,LanguageId,HeadingId,SequenceNo),
> FOREIGN KEY (BrochureId,LanguageId)
> REFERENCES tbBrochure (BrochureId,LanguageId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
>
> -- *** Example 2 SINGLE PRIMARY KEY Code (SQL Server 2000) ***
>
> CREATE TABLE tbLanguage
> (
> LanguageId int identity(1,1) not null,
> LangName varchar(255) not null,
> PRIMARY KEY CLUSTERED (LanguageId)
> )
> go
> CREATE TABLE tbBrochure
> (
> BrochureId int identity(1,1) not null,
> LanguageId int not null,
> Title varchar(255) not null
> PRIMARY KEY CLUSTERED(BrochureId),
> FOREIGN KEY (LanguageId)
> REFERENCES tbLanguage(LanguageId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochure_LanguageId ON tbBrochure
(LanguageId)
> go
> CREATE TABLE tbHeading
> (
> HeadingId int identity(1,1) not null,
> HeadingText varchar(1000) not null,
> PRIMARY KEY CLUSTERED (HeadingId)
> )
> go
> CREATE TABLE tbBrochureHeadingMap
> (
> BrochureHeadingMapId int identity(1,1) not null,
> BrochureId int not null,
> HeadingId int not null,
> PRIMARY KEY CLUSTERED (BrochureHeadingMapId),
> FOREIGN KEY (BrochureId)
> REFERENCES tbBrochure (BrochureId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_BrochureId ON
tbBrochureHeadingMap (BrochureId)
> go
> CREATE NONCLUSTERED INDEX ix_tbBrochureHeadingMap_HeadingId ON
tbBrochureHeadingMap (HeadingId)
> go
> CREATE TABLE tbParagraph
> (
> ParagraphId int identity(1,1) not null,
> HeadingId int not null,
> SequenceNo int not null,
> ParagraphText varchar(4000) not null,
> PRIMARY KEY CLUSTERED (ParagraphId),
> FOREIGN KEY (HeadingId)
> REFERENCES tbHeading (HeadingId)
> )
> go
> CREATE NONCLUSTERED INDEX ix_tbParagraph_BrochureId ON
tbBrochureHeadingMap (HeadingId)
> go
>
>
> It has been argued that Example 1: COMPOSITE FOREIGN KEY has the following
pros, over Example 2:
> 1) Fewer indexes are needed. Five (5) Indexes in Example 1 instead of Nine
(9) in Example 2.
> 2) Queries can be created with fewer joins.
> For example: (one join in Example 1)
> SELECT b.Title,
> p.ParagraphText
> FROM tbBrochure b
> INNER JOIN tbParagraph p
> ON (
> b.BrochureId = p.BrochureId and
> b.LanguageId = p.LanguageId
> )
> Instead Of: (two joins in Example 2)
> SELECT b.Title,
> p.ParagraphText
> FROM tbBrochure b
> INNER JOIN tbBrochureHeadingMap bhm
> ON bhm.BrochureId = b.BrochureId
> INNER JOIN tbParagraph p
> ON p.HeadingId = bhm.HeadingId
> Can anyone see any advantages of using the Example 2 over using Example 1
method ?
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Friday, February 24, 2012

Database Design Help !

Hi !!
We are designing a system where we ask people for their interests and store in into the database and send customize email. Following are the questions:
1) Should we use Identity column as Primary Key and CustomerID column? OR we should create Custom CustomerID and use it as Primary Key? (I have read few articles about Identity column as Primary or not Primary, but need little advice what to accept)
2) We have a Tables called : Interest & Customer_Interest
Customer Table:
CustomerID, Customer Name, Address, Email, Signup Date
Interest Table:

InterestID, InterestName
Customer_Interest: (Need suggestion for How to design this)
Should Table be design like:
Option1: CustomerID, InterestID
Option2: CustomerID, Interest1, Interest2, Interest3, Interest4
i.e.
Lets Say:

Customer table has CustomerA, CustomerB, CustomerC
Interest table has Interest I1, I2, I3, I4
Lets Say CustomerA Signedup for Interest I1, I2, I3 and CustomerB signed up I1, I4
As per Option1:
Customer_Interest Table witll have
CustomerA, I1
CustomerA, I2
CustomerA, I3
CustomerB, I1
CustomerB, I4
OR
As per Option2
Customer_Interest (Where Interest Column is bit column.... 1 = Signed up, 0 = Not Signed up
CustomerA, 1, 1, 1, 0
CustomerB, 1, 0, 0, 1
Which way we should design?
3) If we select Option2, and if we are displaying data in ASP.NET Page, will there be any issue if we use 3 tier architecture?
Thanks !!!You should go with Option 1. It would scale as the interest table changes. Option 2 would be a maintenance nightmare to change as the Interest table changes - not to mention, you would have to interpret the boolean values.|||1) What are the business requirements for identifying a customer? Does the business provide a Customer Number? If the requirements provide a unique identifier, then use what the business provides.
2) As the other poster said, your second option wouldbe a total nightmare. If you really wanted data presented in that view, then you can easily create a view to do that from your normalized table.|||

1) We are going to assign CustomerID. Usually we use Identity Column as CustomerID. After reading these articles, SQL server Forum, this forum etc etc, we started thinking if we should use Identity column as CustomerID or in our system people has to login.. so Can we use Email as Primary Key and use Identity Column as Auto number column or Not to use at all.

2)
I got idea for option 1 and option 2 from reading an article. Interest are going to Change. System will have set of predefined Interest.

Lets say if I go with Option 1:
As a user I selected Interest I1, I2
After sometime (few days) I update my profile. I unregister for Interest I1 but signup for Interest I3.. so now I have Interest I2, I3
In this case, should I remove the row from database for I1, and then add new row to database I3?

|||

1) I've found that IDENTITY are the easiest to use for situations like this. I'd recommend starting them at 10000 to get a consistant length.

2) I'm curious -- I'd be interested in seeing the article you found a suggestion for Option2 in.

The idea is that you have a UserInterest table that contains each users interest -- so every time your interests change, you insert/delete from that table.

If you want a flat view of users with specific interests, just a matter of one join per interest ...
SELECT Username,
CASE WHEN I1.InterestId IS NULL THEN 0 ELSE 1 as Interest1,
CASE WHEN I2.InterestId IS NULL THEN 0 ELSE 1 as Interest2,
CASE WHEN Ix.InterestId IS NULL THEN 0 ELSE 1 as InterestX,
FROM Users
JOIN UserInterests I1 ON Users.UserId = I1.UserId
JOIN UserInterests I2 ON Users.UserId = I2.UserId
JOIN UserInterests IX ON Users.UserId = IX.UserId

|||Alex
1) Here is the article which gave indicated Option 2:
http://www.devx.com/dotnet/Article/20040/0/page/1
2) I liked your suggestion about Starting Identity with 10000 to get consistant length
3) If we use Web Services for Data Access and Business Layer, is it good / bad?
4) How can we apply some software design patterns ? I am looking into MVC but reading few things on web tells me that with .NET 2.0 it has got some issues. Most of our recent development is in .NET 2.0 ? Any ideas?|||

Another Database Design Issue:

We are designing a Shopping Cart forConfectionery Items. For Some Items Customer can select toppings and each toppings cost 0.50$.
Here is Our Sample Table
Products ( 0 = No and 1 = Yes )
ProductID ProductName ProductPrice CanShip CanDeliver
P1 Product 1 25 0 1
P2 product 2 50 1 1
How do we make handle Toppings ?
Lets Say there are 5 toppings options available. For Product 2, customer can choose toppings. How do we handle this in database design?

|||

You'll need a Toppings table, and a table to represnt the one-to-many relationship that you are looking to model.

You will also, of course, need an Orders table. And I would be careful about the way you have the ProductPrice listerd; you might have issues if you decide to change the price...

|||

I have read articles
1)http://www.sqlteam.com/item.asp?ItemID=2599,
2)http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=3804&FORUM_ID=5&CAT_ID=3&Topic_Title=Creating+a+table+of+information+based+from+other+t&Forum_Title=Developer
As per them using Identity Column as Primary Key is not good idea.
Now in our design we have used Identity Column for every table as Primary Key.
Let me write down table
CREATE TABLE [dbo].[AddressBook] (
[AddressBookID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DeliveryZip] (
[ZipCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeliveryRate] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Log] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[EventID] [int] NULL ,
[Category] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Priority] [int] NOT NULL ,
[Severity] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[MachineName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AppDomainName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessID] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ThreadName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32ThreadId] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FormattedMessage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentID] [int] NOT NULL ,
[ShipDate] [datetime] NOT NULL ,
[ShipMethod] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ShipRate] [money] NOT NULL ,
[TaxAmount] [money] NOT NULL ,
[OrderTotal] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Payments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreditCardNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpMonth] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpYear] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressBookID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Prod_Toppings] (
[ProductID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NULL ,
[ModelNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModelName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProductImage] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [varchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CanDeliver] [bit] NOT NULL ,
[CanPickUP] [bit] NOT NULL ,
[CanShip] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart_Toppings] (
[RecordID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Toppings] (
[ToppingID] [int] NOT NULL ,
[ToppingName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

|||

Arbitrarly putting an IDENTITY column on every table is what many of us refer to as an "ID-iot" design. Generally, IDENTITY makes a poor choice as a primary key because it is not predicible or verifiable. But that's a whole other discussion and is something that is very likely out of the scope of your project.
I will comment on your schema though ...
Your naming convention is poor. Data elements should follow ISO-11179 standards. Some examples:
- CanShip should be Shippable_Indicator
- DateCreated should be Creation_Date
- ShoppingCart should be ShoppingCarts
- DeliveryZip should be DeliveryRates
I see no constraints what so ever defined. do you want people to order -6 of an item?
You should avoid the MONEY type. Use DECIMAL instead.
Your model could use some improvement, especially with keys. Eg,
- RecordID is pointless
- An address should be (Cust_Id + Addres_Type)
- OrderTotal should not be in the Orders table. That can be calculated in a view by SUMing the Order_Details

|||

Alex... constraints are defined. However when I generated SQL script I forgot to check that in SQL Server. I will post script again tomorrow once I am at office.

1) Looking at above schema, can you suggest where can we remove Identity Column as Primary Key

2) I agree, we never followed any naming convention standard. Thanks for pointing out. Where can I see ISO-11179 standards? We will work on it.

3) Thanks for pointing Money / Decimal suggestion. Can you tell me why Money should be avoided?
4)
- I got your suggestion for RecordID.
- An address should be (Cust_Id + Addres_Type) -- What does it mean ?
- OrderTotal should not be in the Orders table. That can be calculated in a view by SUMing the Order_Details. -- How do I calculate OrderTotal along with Shipping and Tax from Order_Details

|||Don't take that first SQL Team article too seriously; the gut really has little idea of what he is talking about. (If you don't believe me, look at the comments on the article, where there are actaully CALLS FOR THE ARTICLE TO BE RETRACTED. Don't see that very often.)
There is not anything intrisically wrong with using an identity or guid column, and in fact doing so can provide many benefits. If I were you, I'd research the topic more before taking a scapel to all of those identity keys.|||

Thanks pjmcb for looking into it. Now if I see comments from you and Alex, its contradictory. I will look into suggestions Alex made to me.
In the mean while here is the Schema with Constraints as I promised...
CREATE TABLE [dbo].[AddressBook] (
[AddressBookID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[MI] [varchar] (2) NULL ,
[LastName] [varchar] (50) NOT NULL ,
[Address1] [varchar] (100) NOT NULL ,
[Address2] [varchar] (100) NOT NULL ,
[City] [varchar] (50) NOT NULL ,
[State] [varchar] (2) NOT NULL ,
[Zip] [varchar] (10) NOT NULL ,
[Phone] [varchar] (50) NOT NULL ,
[AddressType] [varchar] (20) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Categories] (
[CategoryName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (50) NOT NULL ,
[Password] [varchar] (50) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DeliveryZip] (
[ZipCode] [varchar] (10) NOT NULL ,
[Location] [varchar] (20) NOT NULL ,
[DeliveryRate] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Log] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[EventID] [int] NULL ,
[Category] [nvarchar] (64) NOT NULL ,
[Priority] [int] NOT NULL ,
[Severity] [nvarchar] (32) NOT NULL ,
[Title] [nvarchar] (256) NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[MachineName] [nvarchar] (32) NOT NULL ,
[AppDomainName] [nvarchar] (2048) NOT NULL ,
[ProcessID] [nvarchar] (256) NOT NULL ,
[ProcessName] [nvarchar] (2048) NOT NULL ,
[ThreadName] [nvarchar] (2048) NULL ,
[Win32ThreadId] [nvarchar] (128) NULL ,
[Message] [nvarchar] (2048) NULL ,
[FormattedMessage] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (10000, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentID] [int] NOT NULL ,
[ShipDate] [datetime] NOT NULL ,
[ShipMethod] [varchar] (20) NOT NULL ,
[ShipRate] [money] NOT NULL ,
[TaxAmount] [money] NOT NULL ,
[OrderTotal] [money] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Payments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [varchar] (50) NOT NULL ,
[CreditCardNo] [varchar] (50) NOT NULL ,
[ExpMonth] [varchar] (50) NOT NULL ,
[ExpYear] [varchar] (50) NOT NULL ,
[AddressBookID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Prod_Toppings] (
[ProductID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1000, 1) NOT NULL ,
[SubCategoryID] [int] NOT NULL ,
[ModelNumber] [varchar] (50) NULL ,
[ModelName] [varchar] (50) NOT NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [varchar] (4000) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50) NULL ,
[Quantity] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ShoppingCart_Toppings] (
[RecordID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SubCategories] (
[SubCategoryID] [int] IDENTITY (100, 1) NOT NULL ,
[SubCategoryName] [varchar] (50) NOT NULL ,
[CategoryName] [varchar] (50) NOT NULL ,
[CanShip] [bit] NULL ,
[CanDeliver] [bit] NULL ,
[CanPickup] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Toppings] (
[ToppingName] [varchar] (50) NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AddressBook] ADD
CONSTRAINT [DF_AddressBook_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
CONSTRAINT [PK_AddressBook] PRIMARY KEY CLUSTERED
(
[AddressBookID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Categories] ADD
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryName]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customers] ADD
CONSTRAINT [DF_Customers_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
CONSTRAINT [PK_Customers] PRIMARY KEY NONCLUSTERED
(
[CustomerID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Customers] UNIQUE NONCLUSTERED
(
[EmailAddress]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Log] ADD
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[LogID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderDetails] ADD
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[ItemID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [DF_Orders_OrderDate] DEFAULT (getdate()) FOR [OrderDate],
CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED
(
[OrderID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Orders] UNIQUE NONCLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Payments] ADD
CONSTRAINT [PK_BillingInfo] PRIMARY KEY CLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [DF_Products_ModelNumber] DEFAULT ('') FOR [ModelNumber],
CONSTRAINT [DF_Products_Description] DEFAULT ('') FOR [Description],
CONSTRAINT [PK_Products] PRIMARY KEY NONCLUSTERED
(
[ProductID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ShoppingCart] ADD
CONSTRAINT [DF_ShoppingCart_Quantity] DEFAULT (1) FOR [Quantity],
CONSTRAINT [DF_ShoppingCart_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
CONSTRAINT [PK_ShoppingCart] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SubCategories] ADD
CONSTRAINT [PK_SubCategories] PRIMARY KEY CLUSTERED
(
[SubCategoryID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Toppings] ADD
CONSTRAINT [PK_Toppings] PRIMARY KEY CLUSTERED
(
[ToppingName]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AddressBook] ADD
CONSTRAINT [FK_AddressBook_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
)
GO

ALTER TABLE [dbo].[OrderDetails] ADD
CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[Orders] (
[OrderID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_OrderDetails_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
)
GO

ALTER TABLE [dbo].[Order_Toppings] ADD
CONSTRAINT [FK_Order_Toppings_OrderDetails] FOREIGN KEY
(
[ItemID]
) REFERENCES [dbo].[OrderDetails] (
[ItemID]
),
CONSTRAINT [FK_Order_Toppings_Toppings] FOREIGN KEY
(
[ToppingName]
) REFERENCES [dbo].[Toppings] (
[ToppingName]
)
GO

ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Payments] FOREIGN KEY
(
[PaymentID]
) REFERENCES [dbo].[Payments] (
[PaymentID]
)
GO

ALTER TABLE [dbo].[Payments] ADD
CONSTRAINT [FK_Payments_AddressBook] FOREIGN KEY
(
[AddressBookID]
) REFERENCES [dbo].[AddressBook] (
[AddressBookID]
)
GO

ALTER TABLE [dbo].[Prod_Toppings] ADD
CONSTRAINT [FK_Prod_Toppings_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
),
CONSTRAINT [FK_Prod_Toppings_Toppings] FOREIGN KEY
(
[ToppingName]
) REFERENCES [dbo].[Toppings] (
[ToppingName]
)
GO

ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [FK_Products_SubCategories] FOREIGN KEY
(
[SubCategoryID]
) REFERENCES [dbo].[SubCategories] (
[SubCategoryID]
)
GO

ALTER TABLE [dbo].[ShoppingCart] ADD
CONSTRAINT [FK_ShoppingCart_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
)
GO

ALTER TABLE [dbo].[ShoppingCart_Toppings] ADD
CONSTRAINT [FK_ShoppingCart_Toppings_ShoppingCart] FOREIGN KEY
(
[RecordID]
) REFERENCES [dbo].[ShoppingCart] (
[RecordID]
)
GO

ALTER TABLE [dbo].[SubCategories] ADD
CONSTRAINT [FK_SubCategories_Categories] FOREIGN KEY
(
[CategoryName]
) REFERENCES [dbo].[Categories] (
[CategoryName]
)
GO

|||

Good to hear you have constriants -- a lot of folks don't use them.
ISO-11179 has now officially been opened up for free. The wikipedia has a very basic article on it, so go search in there for it. I hear it has links to download the standards. The most useful part you will find is part-5. That contains lots of info on how to name your data elements.

The money type is completely worthless and an overall pain to work with. It's propietary and you always need to include a currency symbol infront of it. Makes selecting and querying for it a complete pain in the butt. If your products will be in different currencies, you should split that into two columns (Cost_Amt, Cost_Cur) and use ISO 4217 codes for currency. If you ever need to convert currencies, you can have a simple EXCHANGE_RATES table and do a quick join. Try doing that with MONEY type.

I've looked at your tables, here's where you can improve your keys and other things ...

[AddressBook] - I would name this Addresses or CustomerAddresses. Consider that a customer can have only one address of each type (which I'm assuming is user-defined, like "Home" or "Work"). Therefore, the PK should be (CustId, AddressType).

[Categories] - Who defines these and how will they be used? Generally I would say have CategoryName be the PK, but if it's part of a URL, that can get ugly. I'm assuming that they will be part of the URL, so I would recommend making a Category_Code, 3 or 5 digits (don't know how many or what categories there are), and codifying the categories.

[Customers] - This looks fine although who wants to be Customer #12? seed your identity at 10000, 100000 or whatever you think is one greater than the magnitude of customers (1000's of customers, use 10000, etc).

[DeliveryZip] - I would name this DeliveryRates. ZIP Codes are 5 digits, and only 5 digits (CHAR(5)). ZIP+4 consist of two parts: 5 and 4 digits. Pick which one you want to use, and use it consistantly. Also, I have no idea what Location is. Is this a Longitude/Lattitude? A city name? If you want to use this is a city lookup, that's fine, but include a state. Then you can auto-populate the City/State when customer enters ZIP code.

[Log] - I have no idea how your using log table, generally these are used to just dump logging/tracint data into. I would recommend having your log table be *off* primary table space and contain no primary key. It's just a conviennt way to have a flat file.

[Orders] - I would strongly recommend coming up with a keying scheme for your orders. This will make things monumentally easier for users of the system. It depends on how many orders you will be getting, and that sort of thing, but here is one I use
Y-DDD-SSSS (Y is last digit of year, DDD is day of year, SSSS is a sequence number for the number of orders in that day. should be estimated magnitude+1).

I would store Tax info at the item level -- not all items are taxable, and some items have different tax rates. You can make your view containing the totals really easily:
SELECT O.[OrderId], ..., I.Item_Amount, I.Tax_Total,
I.Item_Amount + I.Tax_Total + O.Shipping_Amount AS Grand_Total
FROM [Orders] O INNER JOIN
(SELECT [OrderId]
SUM([Item_Amount]) AS [Sub_Total],
SUM([Tax_Amount]) AS [Tax_Total]
FROM [Order_Items]
GROUP BY [OrderId]) OI ON O.OrderId = I.OrderId

[Payments] - Wrong name; should be StoredCreditCards, or something to that effect. Payments implies that it contains payments. The primary key should be (CustId, StoredCreditCards_Seq), Seq being a sequential number that starts at one for each customer. Or you could have the customers name their cards. Whjatever.

[Products] - I would also recommend coming up with something other than ProductId as your PK. Is there no SKU to use?

[ShoppingCart] - RecordID is completely unecessary. The PK should be CartID. You may want to consider making a random number/code up for Cart identifiers (I use LEFT(CAST(NEWID() AS VARCHAR(32)),8) By making them sequential and predicable, people can edit thgeir cookies and take control of other carts.

[ShoppingCart_Toppings] - Again record id is unecessary. PK Should be (CartId, ProductId).

|||Alex,
Thanks for your suggestions.
We are looking into it. I don't know if you looked into the revised schema I posted or not.
[AddressBook]:
- We will rename it
- We can't use CustID + AddressType as PK. Since this is a shopping, person can have more than one Shipping or Billing Address. (Something similar to Amazon or BN who allows you to store multiple shipping and billing address)
[Categories]:
- Client Defined. We removed Identity Column and used Category Name as PK
[Customers]:
- Made change as per your suggestion
[DeliveryZip]
- Looking into it
[Log]:
- Error Logs.
[Orders]:
- Will have to think if we can do that or go with Identity starting with 100000 or something.
[Payments]:
- I think we don't need to rename it. Payment actually stores Credit Card info and amount charged on their Credit card. We never show customer their Credit Card info upon their next visit. Payment ID is referenced in Orders Table to know how customer has paid and how much was charged to him including everything.
Correct me if I am wrong.
[Products]:
- Client doesn't have SKU for products. We may go with Identity column starting with 1000 or something.
[ShoppingCart]:
- CartID is not sequential. It is random.
- CartID can't be PK coz, if I add more than 1 item into my cart, CartID is going to get repeated.
E.g.
CartID ProductID Qty
AAAA 1000 2
AAAA 2012 1
-- We have used IBuySpy Portal as our reference and some of the design is based on that. This indicates to us that we can't depend on design like that. Correct?

Sunday, February 19, 2012

Database design

Hi,

I need a hand with designing a database.

I am collecting results from a survey which has the following questions:

Call ref?

How did you place your support call?

Were you satisfied with the amount of time you had to wait until getting acknowledgement of the support call placed?
1 = very satisfied and 10 = very unsatisfied.

How happy were you with the customer service you received upon placing the support call?

1 = very unhappy and 10 = very happy.
How satisfied were you with the amount of time you had to wait until you heard from an engineer?
1 = very satisfied and 10 = very unsatisfied.

How satisfied were you with the time taken to get your problem/query resolved?
1 = very satisfied and 10 = very unsatisfied

Did you feel the engineer had enough knowledge to deal with your call?
1 = very good and 10 = not very good

Overall how satisfied were you with the support call placed?
1 = very satisfied and 10 = very unsatisfied
Is there anything we can do to improve the quality of the support and service you received?

I want to store this in a database. Obviously I want to use best practice for design, normalisation etc. The stumbling block I am coming accross is the fact that each question has a number and each question has a score from 1 to 10 and storing this in the database. Any help appreciated!

Thanks

Andrew

Hi Andrew,

Each question would appear as a separate column. In that column, you would store one value, which is numeric between 1 and 10. With questions like "How did you place your support call", you would provide a dropdownlist of options - each with a key value, and save the key value as an integer. If you offer questions that allow free text answers, you have a choice of text/memo in Access, depending on whether you allow people to submit more than 255 chars, or nvarchar in Sql Server. Personally, I would create a separate table for this type of response, (because most people don't provide answers in free text), and link each response to its parent in the main responses table.

|||

I am using SQL server. I was wondering if there was a way I could make it so I could use the same database table with different survey questions.

So for example, I might have a separate table for questions with a question ID and description, so I could add new questions.

Thanks for the response. Do you have any thoughts on the above?

Thanks

Andrew

|||You could re-use the answers table. No reason why not. You would obviously need a column that holds the identity of each survey. And you would need to be fairly sure that each survey will have a similar question structure. What you might end up with if you are not careful is having to add columns such as Q1a, Q1b, Q1c etc, because someone came up with a questionnaire that had a different structure. You don't want to end up with too many columns that have empty data.|||

I have created one atwww.abtecnet.com/survey.aspx . Is there any software / code / control I can use to display a bar graph of the results?

Thanks

Andrew

|||Crystal Reports is what you are seaching for Big Smile

|||I only want a simple thing. There seem to be a few options about using the vb drawing class?|||

ajw:
I have created one atwww.abtecnet.com/survey.aspx . Is there any software / code / control I can use to display a bar graph of the results?

I would recommend swapping the values so that 1 = poor and 10 = excellent. That's normally the way people are asked to grade things, and your reversal of this may confuse users.

With the charts, there are lots of tutorials on using GDI. I think there may be a new one on the Articles listing on the home page pf www.asp.net right now.

|||

Well spotted. I didnt actually write the questions. I have changed them now so they are a bit more logical .

Cheers

Andrew

|||

I have this code here and I want to populate the array in the page_load event with items from my database. I have tried making a dataset and various other ways but cant seem to get it to do want i want. Any ideas? Thanks Andrew

Imports System.Data
Imports System.Drawing
Imports System.Drawing.Imaging

Public Class Graphics_Barchart
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim aMonths As ArrayList = New ArrayList(), aProfits As ArrayList = New ArrayList()
aMonths.Add("Question1")
aMonths.Add("Question2")
aMonths.Add("Question3")
aMonths.Add("Question4")
aMonths.Add("Question5")
aProfits.Add("1")
aProfits.Add("3")
aProfits.Add("2")
aProfits.Add("3")
aProfits.Add("4")
aProfits.Add("5")
DrawBarGraph("Survey Results", aMonths, aProfits)
End Sub

Sub DrawBarGraph(ByVal strTitle As String, ByVal aX As ArrayList, ByVal aY As ArrayList)
Const iColWidth As Integer = 60, iColSpace As Integer = 25, _
iMaxHeight As Integer = 200, iHeightSpace As Integer = 25, _
iXLegendSpace As Integer = 30, iTitleSpace As Integer = 50
Dim iMaxWidth As Integer = (iColWidth + iColSpace) * aX.Count + iColSpace, iMaxColHeight As Integer = 0, iTotalHeight As Integer = iMaxHeight + iXLegendSpace + iTitleSpace

Dim objBitmap As Bitmap = New Bitmap(iMaxWidth, iTotalHeight)
Dim objGraphics As Graphics = Graphics.FromImage(objBitmap)

objGraphics.FillRectangle(New SolidBrush(Color.White), 0, 0, iMaxWidth, iTotalHeight)
objGraphics.FillRectangle(New SolidBrush(Color.Ivory), 0, 0, iMaxWidth, iMaxHeight)

' find the maximum value
Dim iValue As Integer
For Each iValue In aY
If iValue > iMaxColHeight Then iMaxColHeight = iValue
Next

Dim iBarX As Integer = iColSpace, iCurrentHeight As Integer
Dim objBrush As SolidBrush = New SolidBrush(Color.FromArgb(70, 20, 20))
Dim fontLegend As Font = New Font("Arial", 11), fontValues As Font = New Font("Arial", 8), fontTitle As Font = New Font("Arial", 24)

' loop through and draw each bar
Dim iLoop As Integer
For iLoop = 0 To aX.Count - 1
iCurrentHeight = ((Convert.ToDouble(aY(iLoop)) / Convert.ToDouble(iMaxColHeight)) * Convert.ToDouble(iMaxHeight - iHeightSpace))
objGraphics.FillRectangle(objBrush, iBarX, _
iMaxHeight - iCurrentHeight, iColWidth, iCurrentHeight)
objGraphics.DrawString(aX(iLoop), fontLegend, objBrush, iBarX, iMaxHeight)
objGraphics.DrawString(Format(aY(iLoop), "#,###"), fontValues, objBrush, iBarX, iMaxHeight - iCurrentHeight - 15)
iBarX += (iColSpace + iColWidth)
Next
objGraphics.DrawString(strTitle, fontTitle, objBrush, (iMaxWidth / 2) - strTitle.Length * 6, iMaxHeight + iXLegendSpace)
'objBitmap.Save("C:\inetpub\wwwroot\graph.gif", ImageFormat.GIF)
objBitmap.Save(Response.OutputStream, ImageFormat.Gif)
objGraphics.Dispose()
objBitmap.Dispose()
End Sub

End Class

Thursday, March 29, 2012

Database in loading state

Database in loading state

Database in loading state

Thursday, March 22, 2012

Database export from Vwd 2005

Wednesday, March 7, 2012

Database design question - Using Field definition table

Database design question - Using Field definition table

Saturday, February 25, 2012

Database Design Question

Database Design Question

database design question

Database Design Question

Database Design Question

Friday, February 24, 2012

Database Design Help !

Sunday, February 19, 2012

Database design

Database level lock

Blog Archive

About Me