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.
Saturday, February 25, 2012
Database design question
Hi,
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!
Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact of
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> If yes, where they are saved?
> Thanks for you help!
>
|||Thanks for the quick response. Can you send me the link to the online book on
Create Triggers topic?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> The choice of datawarehouse or datamining really need deciding by analysing
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possible
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact of
> the trigger on any oltp activity. You can also get third party applications
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/pro...ads/books.mspx
2000 -
http://www.microsoft.com/downloads/d...displaylang=en
Then search for CREATE TRIGGER...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/de...asp?frame=true
John
"Mindy" wrote:
[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book on
> Create Triggers topic?
>
> "John Bell" wrote:
I am working on a Budgeting web based application using ASP.NET 2 and SQL
Server 2000. The budgeting data will build up over a course of period and
these historical data will be used for decision making in future budget.
My questions:
1. what design approach should I use to store the historical data? Data
Mining or Datawarehouse? or other options?
2. Is there any built in tool in sql 2000 to keep track of the audit trail?
If yes, where they are saved?
Thanks for you help!
Hi
The choice of datawarehouse or datamining really need deciding by analysing
and producing the requirements for the future needs so can't really be
answered with the level of information you have given.
Although SQL Server 2000 has no automatic method of auditing, it is possible
to implement something using triggers and there is a simple example in the
CREATE TRIGGER topic in Books Online. I would recommend that you do the
mimimum amount of work require in the trigger and do any
aggregation/formatting... as a ofline process. This will reduce the impact of
the trigger on any oltp activity. You can also get third party applications
that implement auditing for you such as Lumigent's auditdb
http://www.lumigent.com/products/auditdb.html
HTH
John
"Mindy" wrote:
> Hi,
> I am working on a Budgeting web based application using ASP.NET 2 and SQL
> Server 2000. The budgeting data will build up over a course of period and
> these historical data will be used for decision making in future budget.
> My questions:
> 1. what design approach should I use to store the historical data? Data
> Mining or Datawarehouse? or other options?
> 2. Is there any built in tool in sql 2000 to keep track of the audit trail?
> If yes, where they are saved?
> Thanks for you help!
>
|||Thanks for the quick response. Can you send me the link to the online book on
Create Triggers topic?
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> The choice of datawarehouse or datamining really need deciding by analysing
> and producing the requirements for the future needs so can't really be
> answered with the level of information you have given.
> Although SQL Server 2000 has no automatic method of auditing, it is possible
> to implement something using triggers and there is a simple example in the
> CREATE TRIGGER topic in Books Online. I would recommend that you do the
> mimimum amount of work require in the trigger and do any
> aggregation/formatting... as a ofline process. This will reduce the impact of
> the trigger on any oltp activity. You can also get third party applications
> that implement auditing for you such as Lumigent's auditdb
> http://www.lumigent.com/products/auditdb.html
> HTH
> John
>
> "Mindy" wrote:
|||You can download Books Online from here:
SQL Server Books Online
2005 -
http://www.microsoft.com/technet/pro...ads/books.mspx
2000 -
http://www.microsoft.com/downloads/d...displaylang=en
Then search for CREATE TRIGGER...
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mindy" <Mindy@.discussions.microsoft.com> wrote in message
news:49F456EE-FBC0-46AD-8257-089E6887AAFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book
> on
> Create Triggers topic?
>
> "John Bell" wrote:
|||Hi
If you don't want to download books online check out
http://msdn.microsoft.com/library/de...asp?frame=true
John
"Mindy" wrote:
[vbcol=seagreen]
> Thanks for the quick response. Can you send me the link to the online book on
> Create Triggers topic?
>
> "John Bell" wrote:
Database Design Question
I have several SQL databases that I am going to create that will all share a
basic part number/part description table, as well as a common customer table.
What is the best way to design this? Should I have a central database that
contains the master tables and then have all of the other tables link to this
table?, or should I set each database with their own copy of this part
number/customer table so they can link to those instead (and then setup some
type of replication)?
I just was not sure if it was good practice to be constantly joining tables
from two different databases each time a SELECT statement is run.
Any help would be appreciated.
Thank you.
Scott Fox, MCAD
Joining across databases is no problem (across instances can be, performancewise). You cannot define
foreign keys, though. So data integrity (RI) has to be done using triggers. There's no best way,
though. Consider advantages and disadvantages for both approaches and use the one that suits you
best. And handle the disadvantages that the solution has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:F9BC8DAB-6122-4BC8-AA45-90DCED9EEE79@.microsoft.com...
>I have several SQL databases that I am going to create that will all share a
> basic part number/part description table, as well as a common customer table.
> What is the best way to design this? Should I have a central database that
> contains the master tables and then have all of the other tables link to this
> table?, or should I set each database with their own copy of this part
> number/customer table so they can link to those instead (and then setup some
> type of replication)?
> I just was not sure if it was good practice to be constantly joining tables
> from two different databases each time a SELECT statement is run.
> Any help would be appreciated.
> Thank you.
> Scott Fox, MCAD
basic part number/part description table, as well as a common customer table.
What is the best way to design this? Should I have a central database that
contains the master tables and then have all of the other tables link to this
table?, or should I set each database with their own copy of this part
number/customer table so they can link to those instead (and then setup some
type of replication)?
I just was not sure if it was good practice to be constantly joining tables
from two different databases each time a SELECT statement is run.
Any help would be appreciated.
Thank you.
Scott Fox, MCAD
Joining across databases is no problem (across instances can be, performancewise). You cannot define
foreign keys, though. So data integrity (RI) has to be done using triggers. There's no best way,
though. Consider advantages and disadvantages for both approaches and use the one that suits you
best. And handle the disadvantages that the solution has.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:F9BC8DAB-6122-4BC8-AA45-90DCED9EEE79@.microsoft.com...
>I have several SQL databases that I am going to create that will all share a
> basic part number/part description table, as well as a common customer table.
> What is the best way to design this? Should I have a central database that
> contains the master tables and then have all of the other tables link to this
> table?, or should I set each database with their own copy of this part
> number/customer table so they can link to those instead (and then setup some
> type of replication)?
> I just was not sure if it was good practice to be constantly joining tables
> from two different databases each time a SELECT statement is run.
> Any help would be appreciated.
> Thank you.
> Scott Fox, MCAD
Database Design Question
I am right now in process of designing a database for hosting business. Now
just like other hosting companies even this hosting companies has its
different web hosting packages. But besides that the company is going to
provide a feature to customer where in they can select/customize the package
wherein they might want to add one of two additional feature that are not a
part of the standard package.
I have designed most of that tables but i am confused on how exactly should
i design the "User Defined Package" table.
There are one of the two things that i can do.
1) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have a "featureid" (integer datatype) column that
would be referencing "Features" table.
The problem here is that it would be easily be managable from programming
aspect but there wil be redundancy factor since if a same customer takes 5
additional features then there would be 5 rows with same customer id and
separate featureid and this is just for one customer. If there is a large
customer base it could create space issues as well.
2) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have another column featureids (varchar datatype)
that would have all the additional feature ids seperated by a deliminator.
There won't be redundancy in this case but would make things little bit
complicated from programming aspect since everything new additional feature
to to be added or edited or to be removed will require some work in the
code.
Which method should i go for that would be helpful not just now but also in
future as the customer base increases.
I do not have anything else in mind. If there is any other solution to this
all the suggestions are welcomed.
Thank you
Niel
Niel wrote:
> I am right now in process of designing a database for hosting business. Now
> just like other hosting companies even this hosting companies has its
> different web hosting packages. But besides that the company is going to
> provide a feature to customer where in they can select/customize the package
> wherein they might want to add one of two additional feature that are not a
> part of the standard package.
> I have designed most of that tables but i am confused on how exactly should
> i design the "User Defined Package" table.
> There are one of the two things that i can do.
> 1) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have a "featureid" (integer datatype) column that
> would be referencing "Features" table.
> The problem here is that it would be easily be managable from programming
> aspect but there wil be redundancy factor since if a same customer takes 5
> additional features then there would be 5 rows with same customer id and
> separate featureid and this is just for one customer. If there is a large
> customer base it could create space issues as well.
> 2) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have another column featureids (varchar datatype)
> that would have all the additional feature ids seperated by a deliminator.
> There won't be redundancy in this case but would make things little bit
> complicated from programming aspect since everything new additional feature
> to to be added or edited or to be removed will require some work in the
> code.
> Which method should i go for that would be helpful not just now but also in
> future as the customer base increases.
> I do not have anything else in mind. If there is any other solution to this
> all the suggestions are welcomed.
> Thank you
> Niel
I recommend you study some books or take a course on database design
theory before you go further. Your option 2 is a textbook example of
how NOT to do it.
Your first option sounds right to me from the point of view of
scalability and integrity. Of course I haven't had the opportunity to
analyse your business requirements, I only have your narrative to go
on. That's why newsgroups are a poor place to get database design
advice.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks for the suggestion david,
I'll go ahead and have a look at few book for reference. Can you advise me
on any good places/tutorials on website that i can go through to get a clear
concept on this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143574016.274320.221210@.v46g2000cwv.googlegr oups.com...[vbcol=seagreen]
> Niel wrote:
Now[vbcol=seagreen]
package[vbcol=seagreen]
not a[vbcol=seagreen]
should[vbcol=seagreen]
referencing[vbcol=seagreen]
that[vbcol=seagreen]
programming[vbcol=seagreen]
5[vbcol=seagreen]
large[vbcol=seagreen]
referencing[vbcol=seagreen]
datatype)[vbcol=seagreen]
deliminator.[vbcol=seagreen]
feature[vbcol=seagreen]
in[vbcol=seagreen]
this
> I recommend you study some books or take a course on database design
> theory before you go further. Your option 2 is a textbook example of
> how NOT to do it.
> Your first option sounds right to me from the point of view of
> scalability and integrity. Of course I haven't had the opportunity to
> analyse your business requirements, I only have your narrative to go
> on. That's why newsgroups are a poor place to get database design
> advice.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
just like other hosting companies even this hosting companies has its
different web hosting packages. But besides that the company is going to
provide a feature to customer where in they can select/customize the package
wherein they might want to add one of two additional feature that are not a
part of the standard package.
I have designed most of that tables but i am confused on how exactly should
i design the "User Defined Package" table.
There are one of the two things that i can do.
1) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have a "featureid" (integer datatype) column that
would be referencing "Features" table.
The problem here is that it would be easily be managable from programming
aspect but there wil be redundancy factor since if a same customer takes 5
additional features then there would be 5 rows with same customer id and
separate featureid and this is just for one customer. If there is a large
customer base it could create space issues as well.
2) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have another column featureids (varchar datatype)
that would have all the additional feature ids seperated by a deliminator.
There won't be redundancy in this case but would make things little bit
complicated from programming aspect since everything new additional feature
to to be added or edited or to be removed will require some work in the
code.
Which method should i go for that would be helpful not just now but also in
future as the customer base increases.
I do not have anything else in mind. If there is any other solution to this
all the suggestions are welcomed.
Thank you
Niel
Niel wrote:
> I am right now in process of designing a database for hosting business. Now
> just like other hosting companies even this hosting companies has its
> different web hosting packages. But besides that the company is going to
> provide a feature to customer where in they can select/customize the package
> wherein they might want to add one of two additional feature that are not a
> part of the standard package.
> I have designed most of that tables but i am confused on how exactly should
> i design the "User Defined Package" table.
> There are one of the two things that i can do.
> 1) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have a "featureid" (integer datatype) column that
> would be referencing "Features" table.
> The problem here is that it would be easily be managable from programming
> aspect but there wil be redundancy factor since if a same customer takes 5
> additional features then there would be 5 rows with same customer id and
> separate featureid and this is just for one customer. If there is a large
> customer base it could create space issues as well.
> 2) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have another column featureids (varchar datatype)
> that would have all the additional feature ids seperated by a deliminator.
> There won't be redundancy in this case but would make things little bit
> complicated from programming aspect since everything new additional feature
> to to be added or edited or to be removed will require some work in the
> code.
> Which method should i go for that would be helpful not just now but also in
> future as the customer base increases.
> I do not have anything else in mind. If there is any other solution to this
> all the suggestions are welcomed.
> Thank you
> Niel
I recommend you study some books or take a course on database design
theory before you go further. Your option 2 is a textbook example of
how NOT to do it.
Your first option sounds right to me from the point of view of
scalability and integrity. Of course I haven't had the opportunity to
analyse your business requirements, I only have your narrative to go
on. That's why newsgroups are a poor place to get database design
advice.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Thanks for the suggestion david,
I'll go ahead and have a look at few book for reference. Can you advise me
on any good places/tutorials on website that i can go through to get a clear
concept on this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143574016.274320.221210@.v46g2000cwv.googlegr oups.com...[vbcol=seagreen]
> Niel wrote:
Now[vbcol=seagreen]
package[vbcol=seagreen]
not a[vbcol=seagreen]
should[vbcol=seagreen]
referencing[vbcol=seagreen]
that[vbcol=seagreen]
programming[vbcol=seagreen]
5[vbcol=seagreen]
large[vbcol=seagreen]
referencing[vbcol=seagreen]
datatype)[vbcol=seagreen]
deliminator.[vbcol=seagreen]
feature[vbcol=seagreen]
in[vbcol=seagreen]
this
> I recommend you study some books or take a course on database design
> theory before you go further. Your option 2 is a textbook example of
> how NOT to do it.
> Your first option sounds right to me from the point of view of
> scalability and integrity. Of course I haven't had the opportunity to
> analyse your business requirements, I only have your narrative to go
> on. That's why newsgroups are a poor place to get database design
> advice.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
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.
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
I am attempting to develop a forum. I have the pages I need and was just
curious if anyone here knows, or if there is a website, how to setup a
database for a forum? Basically, the columns is what I need.Nathan
<http://www.databaseanswers.com/data_models/index.htm> -- examples
database design
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:658A4F34-2AAC-4DAD-A42D-5FED367E48FA@.microsoft.com...
> I am attempting to develop a forum. I have the pages I need and was just
> curious if anyone here knows, or if there is a website, how to setup a
> database for a forum? Basically, the columns is what I need.|||If you are wanting to build your own database from scratch, the best advice
is to make a list of everything you want to include in your forums.
Everything. Then fill each item into a database design. Normalize it, and
you will have what you want. It is probably not as easy as it sounds, but
it is not all that hard either.
If you are looking for things to put into a forum design, look at the
website Uri gave you, and then hit lots of other forums to help make your
list of features that require data (and those that don't)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:658A4F34-2AAC-4DAD-A42D-5FED367E48FA@.microsoft.com...
>I am attempting to develop a forum. I have the pages I need and was just
> curious if anyone here knows, or if there is a website, how to setup a
> database for a forum? Basically, the columns is what I need.
curious if anyone here knows, or if there is a website, how to setup a
database for a forum? Basically, the columns is what I need.Nathan
<http://www.databaseanswers.com/data_models/index.htm> -- examples
database design
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:658A4F34-2AAC-4DAD-A42D-5FED367E48FA@.microsoft.com...
> I am attempting to develop a forum. I have the pages I need and was just
> curious if anyone here knows, or if there is a website, how to setup a
> database for a forum? Basically, the columns is what I need.|||If you are wanting to build your own database from scratch, the best advice
is to make a list of everything you want to include in your forums.
Everything. Then fill each item into a database design. Normalize it, and
you will have what you want. It is probably not as easy as it sounds, but
it is not all that hard either.
If you are looking for things to put into a forum design, look at the
website Uri gave you, and then hit lots of other forums to help make your
list of features that require data (and those that don't)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:658A4F34-2AAC-4DAD-A42D-5FED367E48FA@.microsoft.com...
>I am attempting to develop a forum. I have the pages I need and was just
> curious if anyone here knows, or if there is a website, how to setup a
> database for a forum? Basically, the columns is what I need.
Database design question
I have a series of database objects that represent things such as people,
accounts, etc. I have a set of options (boolean) that I need to add to
these objects. Normally I would just create a bit field for each one and be
done with it. The challenge however is that there could be hundreds of
these options (maybe 300) and a user decides which of these options
(actually attributes) that should apply to these db objects.
For example, a user can create a new type of person object. What makes this
person object different is the fact that it has a unique set of these
attributes. So whenever someone creates a new instance of this person
object (another one), it contains these attributes (whose values can be set
uniquely).
These attributes are mainly for searching, in other words, you can flag an
account with some of these attributes and then search on them. In terms of
database design, what is a good approach? I was thinking of having a master
list of all the available attributes in a separate table. Then a separate
table containing the mappings to the types (e.g. persontype1 has attribute
1,3,4,5,6...). Then a third table containing the specific instances of
these persons and their value (e.g. 'bob' of persontype1 values for 1=on,
3=off, etc.).
If this is the case, how would would one search on these items? It seems
that the code to build the searching (considering all the items are dynamic)
would be very ugly.
Sorry if this isn't totally clear, this is actually the first time I am
explaining it on paper (well sort of).>> I have a series of database objects that represent things such as
people, accounts, etc. I have a set of options (boolean) that I need
to add to these objects. Normally I would just create a bit field
[sic] for each one and be
done with it. <<
Series? SQL uses sets. Booleans? SQL has no boolean data type. You
are probably about to detroy your data integrity with a EAV design.
options [sic] (maybe 300) <<
Options? Well, at least you know they are really attributes. An
object does not have optional attributes; it is the sum of all its
attributes in an RDBMS. This is foundations, not rocket science.
should apply to these db objects. <<
I certainly hope not! You are supposed to know what you are doing and
not let any random user design the schema.
You then describe mixing data and metadata in such a way that you will
never have data integrity. Stop what you are doing and read a book
RDBMS basics; you missed the whole concept.|||300 *boolean* attributes! I wonder how many you would really need if
you modelled the same attributes with well-chosen codes and valued
attributes instead of check boxes (which I bet is the origin of this
design). 300 bits of data is actually very little.
Represent classes and sub-classes with the common attributes in a
common table and the specific attributes in related tables sharing the
same primary key. Optional attributes can also be nullable or use
tokens for the inapplicable values.
David Portas
SQL Server MVP
--|||Tim Mavers wrote:
> I have a series of database objects that represent things such as people,
> accounts, etc. I have a set of options (boolean) that I need to add to
> these objects. Normally I would just create a bit field for each one and
be
> done with it. The challenge however is that there could be hundreds of
> these options (maybe 300) and a user decides which of these options
> (actually attributes) that should apply to these db objects.
> For example, a user can create a new type of person object. What makes th
is
> person object different is the fact that it has a unique set of these
> attributes. So whenever someone creates a new instance of this person
> object (another one), it contains these attributes (whose values can be se
t
> uniquely).
> These attributes are mainly for searching, in other words, you can flag an
> account with some of these attributes and then search on them. In terms o
f
> database design, what is a good approach? I was thinking of having a mast
er
> list of all the available attributes in a separate table. Then a separate
> table containing the mappings to the types (e.g. persontype1 has attribute
> 1,3,4,5,6...). Then a third table containing the specific instances of
> these persons and their value (e.g. 'bob' of persontype1 values for 1=on,
> 3=off, etc.).
> If this is the case, how would would one search on these items? It seems
> that the code to build the searching (considering all the items are dynami
c)
> would be very ugly.
> Sorry if this isn't totally clear, this is actually the first time I am
> explaining it on paper (well sort of).
>
I think you've done a good job with design if I understood you correctly.
I presume you have a 3 tables: Person, Attribute and PersonAttribute.
PersonAtribute is N:N link that has PersonID and AttributeID.
One way to preform search is to create a stored procedure which will
find all persons who have a set of attributes linked to it. Pass the
selected attributes to a procedure as a parameter and then join them
with Persons thru PersonAttribute.|||You will either have the 300 characteristics in the person table or slap it
in an "enumeration" table. With the second direction, you put 300 bools in a
"profile" type of table and then link the person to a particular profile
based on the answers to the question. This will make the initial insert
slower, as you will have to check to determine which "type" of person you ar
e
dealing with.
The other option is to determine ways of grouping this information (break
booleans down to groupings), but you will still end up with either embedding
this info in your table or placing it in a "profile" table.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Tim Mavers" wrote:
> I have a series of database objects that represent things such as people,
> accounts, etc. I have a set of options (boolean) that I need to add to
> these objects. Normally I would just create a bit field for each one and
be
> done with it. The challenge however is that there could be hundreds of
> these options (maybe 300) and a user decides which of these options
> (actually attributes) that should apply to these db objects.
> For example, a user can create a new type of person object. What makes th
is
> person object different is the fact that it has a unique set of these
> attributes. So whenever someone creates a new instance of this person
> object (another one), it contains these attributes (whose values can be se
t
> uniquely).
> These attributes are mainly for searching, in other words, you can flag an
> account with some of these attributes and then search on them. In terms o
f
> database design, what is a good approach? I was thinking of having a mast
er
> list of all the available attributes in a separate table. Then a separate
> table containing the mappings to the types (e.g. persontype1 has attribute
> 1,3,4,5,6...). Then a third table containing the specific instances of
> these persons and their value (e.g. 'bob' of persontype1 values for 1=on,
> 3=off, etc.).
> If this is the case, how would would one search on these items? It seems
> that the code to build the searching (considering all the items are dynami
c)
> would be very ugly.
> Sorry if this isn't totally clear, this is actually the first time I am
> explaining it on paper (well sort of).
>
>|||Tim Mavers (webview@.hotmail.com) writes:
> These attributes are mainly for searching, in other words, you can flag
> an account with some of these attributes and then search on them. In
> terms of database design, what is a good approach? I was thinking of
> having a master list of all the available attributes in a separate
> table. Then a separate table containing the mappings to the types (e.g.
> persontype1 has attribute 1,3,4,5,6...). Then a third table containing
> the specific instances of these persons and their value (e.g. 'bob' of
> persontype1 values for 1=on, 3=off, etc.).
Hm, if I understand this correctly, you appear to need more tables.
First there is:
CREATE TABLE attributes (attributeid int NOT NULL,
attributename varchar(50) NOT NULL,
CONSTRAINT pk_attributes PRIMARY KEY (attributeid))
Then you have:
CREATE TABLE persontyoes (persontypeid int NOT NULL,
persontypename varchar(50) NOT NULL,
CONSTRAINT pk_persontypes PRIMARY KEY (persontypeid))
Which defines the possible persontypes. Whether this should really be
an objecttypes table, or you should have one table for persons, another
for accounts etc I can't really tell with the scant information that I
have.
Then to define which attributes that are possible for a person type:
CREATE TABLE persontypeattributes (persontypeid NOT NULL,
attributeid NOT NULL,
CONSTRAINT pk_personstypeattr PRIMARY KEY (persontypeid,
attreibuteid), CONSTRAINT fk1_persontype FORIEGN KEY (persontypeid)
REFERENCES persontypes (persontypeid),
CONSTRAINT fk2_attribute FOREIGN KEY (attributeid)
REFERNCES attributes (attributeid)
Then you would need a column in the persons table to identify the
persontype - or if a person can belong to more than one person table,
you need a personpersontypes table. And finally you would need a
personattributes table. This last table is a little tricky, because
you somehow need to ascertain that the attributes applicable to the
person's person type(s) go into the table. You probably need a trigger
for that.
> If this is the case, how would would one search on these items? It
> seems that the code to build the searching (considering all the items
> are dynamic) would be very ugly.
I'm not really sure how these searches really looks like. But if a user
searches for users with certain settings flags of five attributes (and
the rest thus "don't care"), I think you could do something like:
SELECT personid
FROM personatttributes pa
JOIN searchcriteris sa ON pa.attributeid = sa.attributeid
AND pa.attributeval = sa.attributeval
WHERE sa.searchkey = @.searchkey
GROUP BY personid
HAVING COUNT(*) = (SELECT COUNT(*)
FROM searchcriteria
WHERE searchkey = @.searchkey)
That is, you would shove down the uses choices in a table, and identify
each search with some session key.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the all the replies so far. Right now I have:
Person Table (name, phone, etc.)
I have a pre-set list of attributes (attributes for a person). These mostly
consist of yes/no values, but some are can contain numbers or text.
Functionally, special PersonTypes (like MarketingPerson, EducationalPerson,
etc.) need to be created. What makes them a specific type is the set of
these attributes (for lack of a better term) that needs to be assigned to
them. So again funcitonally, someone will go in and define a
"MarketingPerson" and select one or more of these values to be included.
When someone creates a new MarketingPerson, those new attributes (booleans,
text fields,etc) are available even though they really aren't part of the
"Person" table.
I am concerned about searching? Having so many attributes available can
generate a really ugly (and large) query, correct?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns960E128F8CDAYazorman@.127.0.0.1...
> Tim Mavers (webview@.hotmail.com) writes:
> Hm, if I understand this correctly, you appear to need more tables.
> First there is:
> CREATE TABLE attributes (attributeid int NOT NULL,
> attributename varchar(50) NOT NULL,
> CONSTRAINT pk_attributes PRIMARY KEY (attributeid))
> Then you have:
> CREATE TABLE persontyoes (persontypeid int NOT NULL,
> persontypename varchar(50) NOT NULL,
> CONSTRAINT pk_persontypes PRIMARY KEY (persontypeid))
> Which defines the possible persontypes. Whether this should really be
> an objecttypes table, or you should have one table for persons, another
> for accounts etc I can't really tell with the scant information that I
> have.
> Then to define which attributes that are possible for a person type:
> CREATE TABLE persontypeattributes (persontypeid NOT NULL,
> attributeid NOT NULL,
> CONSTRAINT pk_personstypeattr PRIMARY KEY (persontypeid,
> attreibuteid), CONSTRAINT fk1_persontype FORIEGN KEY (persontypeid)
> REFERENCES persontypes (persontypeid),
> CONSTRAINT fk2_attribute FOREIGN KEY (attributeid)
> REFERNCES attributes (attributeid)
> Then you would need a column in the persons table to identify the
> persontype - or if a person can belong to more than one person table,
> you need a personpersontypes table. And finally you would need a
> personattributes table. This last table is a little tricky, because
> you somehow need to ascertain that the attributes applicable to the
> person's person type(s) go into the table. You probably need a trigger
> for that.
>
> I'm not really sure how these searches really looks like. But if a user
> searches for users with certain settings flags of five attributes (and
> the rest thus "don't care"), I think you could do something like:
> SELECT personid
> FROM personatttributes pa
> JOIN searchcriteris sa ON pa.attributeid = sa.attributeid
> AND pa.attributeval = sa.attributeval
> WHERE sa.searchkey = @.searchkey
> GROUP BY personid
> HAVING COUNT(*) = (SELECT COUNT(*)
> FROM searchcriteria
> WHERE searchkey = @.searchkey)
> That is, you would shove down the uses choices in a table, and identify
> each search with some session key.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Tim Mavers (webview@.hotmail.com) writes:
> I have a pre-set list of attributes (attributes for a person). These
> mostly consist of yes/no values, but some are can contain numbers or
> text.
This can be covered by sql_sqlvariant. The attribute definition would
have a column that defines the data type. This can then be enforced
in a trigger by using sql_variant_property() to find the current data
type.
> I am concerned about searching? Having so many attributes available can
> generate a really ugly (and large) query, correct?
Did you look at this query that I proposed:
Doesn't look ugly to me. However, it may not be effective. In fact, since
I only made it up, it may not even work.
I would be essential to enforce the actual data types for the sql_variants
in searchcriterias too.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
accounts, etc. I have a set of options (boolean) that I need to add to
these objects. Normally I would just create a bit field for each one and be
done with it. The challenge however is that there could be hundreds of
these options (maybe 300) and a user decides which of these options
(actually attributes) that should apply to these db objects.
For example, a user can create a new type of person object. What makes this
person object different is the fact that it has a unique set of these
attributes. So whenever someone creates a new instance of this person
object (another one), it contains these attributes (whose values can be set
uniquely).
These attributes are mainly for searching, in other words, you can flag an
account with some of these attributes and then search on them. In terms of
database design, what is a good approach? I was thinking of having a master
list of all the available attributes in a separate table. Then a separate
table containing the mappings to the types (e.g. persontype1 has attribute
1,3,4,5,6...). Then a third table containing the specific instances of
these persons and their value (e.g. 'bob' of persontype1 values for 1=on,
3=off, etc.).
If this is the case, how would would one search on these items? It seems
that the code to build the searching (considering all the items are dynamic)
would be very ugly.
Sorry if this isn't totally clear, this is actually the first time I am
explaining it on paper (well sort of).>> I have a series of database objects that represent things such as
people, accounts, etc. I have a set of options (boolean) that I need
to add to these objects. Normally I would just create a bit field
[sic] for each one and be
done with it. <<
Series? SQL uses sets. Booleans? SQL has no boolean data type. You
are probably about to detroy your data integrity with a EAV design.
options [sic] (maybe 300) <<
Options? Well, at least you know they are really attributes. An
object does not have optional attributes; it is the sum of all its
attributes in an RDBMS. This is foundations, not rocket science.
should apply to these db objects. <<
I certainly hope not! You are supposed to know what you are doing and
not let any random user design the schema.
You then describe mixing data and metadata in such a way that you will
never have data integrity. Stop what you are doing and read a book
RDBMS basics; you missed the whole concept.|||300 *boolean* attributes! I wonder how many you would really need if
you modelled the same attributes with well-chosen codes and valued
attributes instead of check boxes (which I bet is the origin of this
design). 300 bits of data is actually very little.
Represent classes and sub-classes with the common attributes in a
common table and the specific attributes in related tables sharing the
same primary key. Optional attributes can also be nullable or use
tokens for the inapplicable values.
David Portas
SQL Server MVP
--|||Tim Mavers wrote:
> I have a series of database objects that represent things such as people,
> accounts, etc. I have a set of options (boolean) that I need to add to
> these objects. Normally I would just create a bit field for each one and
be
> done with it. The challenge however is that there could be hundreds of
> these options (maybe 300) and a user decides which of these options
> (actually attributes) that should apply to these db objects.
> For example, a user can create a new type of person object. What makes th
is
> person object different is the fact that it has a unique set of these
> attributes. So whenever someone creates a new instance of this person
> object (another one), it contains these attributes (whose values can be se
t
> uniquely).
> These attributes are mainly for searching, in other words, you can flag an
> account with some of these attributes and then search on them. In terms o
f
> database design, what is a good approach? I was thinking of having a mast
er
> list of all the available attributes in a separate table. Then a separate
> table containing the mappings to the types (e.g. persontype1 has attribute
> 1,3,4,5,6...). Then a third table containing the specific instances of
> these persons and their value (e.g. 'bob' of persontype1 values for 1=on,
> 3=off, etc.).
> If this is the case, how would would one search on these items? It seems
> that the code to build the searching (considering all the items are dynami
c)
> would be very ugly.
> Sorry if this isn't totally clear, this is actually the first time I am
> explaining it on paper (well sort of).
>
I think you've done a good job with design if I understood you correctly.
I presume you have a 3 tables: Person, Attribute and PersonAttribute.
PersonAtribute is N:N link that has PersonID and AttributeID.
One way to preform search is to create a stored procedure which will
find all persons who have a set of attributes linked to it. Pass the
selected attributes to a procedure as a parameter and then join them
with Persons thru PersonAttribute.|||You will either have the 300 characteristics in the person table or slap it
in an "enumeration" table. With the second direction, you put 300 bools in a
"profile" type of table and then link the person to a particular profile
based on the answers to the question. This will make the initial insert
slower, as you will have to check to determine which "type" of person you ar
e
dealing with.
The other option is to determine ways of grouping this information (break
booleans down to groupings), but you will still end up with either embedding
this info in your table or placing it in a "profile" table.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Tim Mavers" wrote:
> I have a series of database objects that represent things such as people,
> accounts, etc. I have a set of options (boolean) that I need to add to
> these objects. Normally I would just create a bit field for each one and
be
> done with it. The challenge however is that there could be hundreds of
> these options (maybe 300) and a user decides which of these options
> (actually attributes) that should apply to these db objects.
> For example, a user can create a new type of person object. What makes th
is
> person object different is the fact that it has a unique set of these
> attributes. So whenever someone creates a new instance of this person
> object (another one), it contains these attributes (whose values can be se
t
> uniquely).
> These attributes are mainly for searching, in other words, you can flag an
> account with some of these attributes and then search on them. In terms o
f
> database design, what is a good approach? I was thinking of having a mast
er
> list of all the available attributes in a separate table. Then a separate
> table containing the mappings to the types (e.g. persontype1 has attribute
> 1,3,4,5,6...). Then a third table containing the specific instances of
> these persons and their value (e.g. 'bob' of persontype1 values for 1=on,
> 3=off, etc.).
> If this is the case, how would would one search on these items? It seems
> that the code to build the searching (considering all the items are dynami
c)
> would be very ugly.
> Sorry if this isn't totally clear, this is actually the first time I am
> explaining it on paper (well sort of).
>
>|||Tim Mavers (webview@.hotmail.com) writes:
> These attributes are mainly for searching, in other words, you can flag
> an account with some of these attributes and then search on them. In
> terms of database design, what is a good approach? I was thinking of
> having a master list of all the available attributes in a separate
> table. Then a separate table containing the mappings to the types (e.g.
> persontype1 has attribute 1,3,4,5,6...). Then a third table containing
> the specific instances of these persons and their value (e.g. 'bob' of
> persontype1 values for 1=on, 3=off, etc.).
Hm, if I understand this correctly, you appear to need more tables.
First there is:
CREATE TABLE attributes (attributeid int NOT NULL,
attributename varchar(50) NOT NULL,
CONSTRAINT pk_attributes PRIMARY KEY (attributeid))
Then you have:
CREATE TABLE persontyoes (persontypeid int NOT NULL,
persontypename varchar(50) NOT NULL,
CONSTRAINT pk_persontypes PRIMARY KEY (persontypeid))
Which defines the possible persontypes. Whether this should really be
an objecttypes table, or you should have one table for persons, another
for accounts etc I can't really tell with the scant information that I
have.
Then to define which attributes that are possible for a person type:
CREATE TABLE persontypeattributes (persontypeid NOT NULL,
attributeid NOT NULL,
CONSTRAINT pk_personstypeattr PRIMARY KEY (persontypeid,
attreibuteid), CONSTRAINT fk1_persontype FORIEGN KEY (persontypeid)
REFERENCES persontypes (persontypeid),
CONSTRAINT fk2_attribute FOREIGN KEY (attributeid)
REFERNCES attributes (attributeid)
Then you would need a column in the persons table to identify the
persontype - or if a person can belong to more than one person table,
you need a personpersontypes table. And finally you would need a
personattributes table. This last table is a little tricky, because
you somehow need to ascertain that the attributes applicable to the
person's person type(s) go into the table. You probably need a trigger
for that.
> If this is the case, how would would one search on these items? It
> seems that the code to build the searching (considering all the items
> are dynamic) would be very ugly.
I'm not really sure how these searches really looks like. But if a user
searches for users with certain settings flags of five attributes (and
the rest thus "don't care"), I think you could do something like:
SELECT personid
FROM personatttributes pa
JOIN searchcriteris sa ON pa.attributeid = sa.attributeid
AND pa.attributeval = sa.attributeval
WHERE sa.searchkey = @.searchkey
GROUP BY personid
HAVING COUNT(*) = (SELECT COUNT(*)
FROM searchcriteria
WHERE searchkey = @.searchkey)
That is, you would shove down the uses choices in a table, and identify
each search with some session key.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the all the replies so far. Right now I have:
Person Table (name, phone, etc.)
I have a pre-set list of attributes (attributes for a person). These mostly
consist of yes/no values, but some are can contain numbers or text.
Functionally, special PersonTypes (like MarketingPerson, EducationalPerson,
etc.) need to be created. What makes them a specific type is the set of
these attributes (for lack of a better term) that needs to be assigned to
them. So again funcitonally, someone will go in and define a
"MarketingPerson" and select one or more of these values to be included.
When someone creates a new MarketingPerson, those new attributes (booleans,
text fields,etc) are available even though they really aren't part of the
"Person" table.
I am concerned about searching? Having so many attributes available can
generate a really ugly (and large) query, correct?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns960E128F8CDAYazorman@.127.0.0.1...
> Tim Mavers (webview@.hotmail.com) writes:
> Hm, if I understand this correctly, you appear to need more tables.
> First there is:
> CREATE TABLE attributes (attributeid int NOT NULL,
> attributename varchar(50) NOT NULL,
> CONSTRAINT pk_attributes PRIMARY KEY (attributeid))
> Then you have:
> CREATE TABLE persontyoes (persontypeid int NOT NULL,
> persontypename varchar(50) NOT NULL,
> CONSTRAINT pk_persontypes PRIMARY KEY (persontypeid))
> Which defines the possible persontypes. Whether this should really be
> an objecttypes table, or you should have one table for persons, another
> for accounts etc I can't really tell with the scant information that I
> have.
> Then to define which attributes that are possible for a person type:
> CREATE TABLE persontypeattributes (persontypeid NOT NULL,
> attributeid NOT NULL,
> CONSTRAINT pk_personstypeattr PRIMARY KEY (persontypeid,
> attreibuteid), CONSTRAINT fk1_persontype FORIEGN KEY (persontypeid)
> REFERENCES persontypes (persontypeid),
> CONSTRAINT fk2_attribute FOREIGN KEY (attributeid)
> REFERNCES attributes (attributeid)
> Then you would need a column in the persons table to identify the
> persontype - or if a person can belong to more than one person table,
> you need a personpersontypes table. And finally you would need a
> personattributes table. This last table is a little tricky, because
> you somehow need to ascertain that the attributes applicable to the
> person's person type(s) go into the table. You probably need a trigger
> for that.
>
> I'm not really sure how these searches really looks like. But if a user
> searches for users with certain settings flags of five attributes (and
> the rest thus "don't care"), I think you could do something like:
> SELECT personid
> FROM personatttributes pa
> JOIN searchcriteris sa ON pa.attributeid = sa.attributeid
> AND pa.attributeval = sa.attributeval
> WHERE sa.searchkey = @.searchkey
> GROUP BY personid
> HAVING COUNT(*) = (SELECT COUNT(*)
> FROM searchcriteria
> WHERE searchkey = @.searchkey)
> That is, you would shove down the uses choices in a table, and identify
> each search with some session key.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Tim Mavers (webview@.hotmail.com) writes:
> I have a pre-set list of attributes (attributes for a person). These
> mostly consist of yes/no values, but some are can contain numbers or
> text.
This can be covered by sql_sqlvariant. The attribute definition would
have a column that defines the data type. This can then be enforced
in a trigger by using sql_variant_property() to find the current data
type.
> I am concerned about searching? Having so many attributes available can
> generate a really ugly (and large) query, correct?
Did you look at this query that I proposed:
Doesn't look ugly to me. However, it may not be effective. In fact, since
I only made it up, it may not even work.
I would be essential to enforce the actual data types for the sql_variants
in searchcriterias too.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)