Is this table design correct?

Hey Everyone,

Just had a quick question for you . I have these 3 tables that are related in some way - below are the structures. I know the structures are correct and they work fine. However, I am using Visio Enterprise Architect to design these tables and when I try to generate the code, I get an error saying that it does not like the table with 2 fields as the primary key. And I just wanted to get feedback to find out whether I am correct or Visio is.

Table: Orders
Columns:
OrderID int PK
Name varchar(100)
Phone varchar(20)

Table: Products
Columns:
ProductID int PK
Name varchar(100)

Table: OrderLineItems
Columns:
OrderID int PK
ProductID int PK
Price money

Thanks for any feedback.

Johnny DevA composite primary key is perfectly legitimate and is often useful if you have a link table, like in your case. The only thing that puzzles me is the Price column in the OrderLineItems table - shouldn't it be in the Products table?|||Hi Diplo,

Thanks for the response. This clears things up. Visio needs to be updated to support this.

The table structure that I posted in this message is not what I have. It was just a sample to demonstrate my case so I wont post my real tables (too complicated :). In fact, my tables are not even Order/Products related. But thanks anyway for catching that for others to see.sql

IS THIS GOOD DESIGN?

I have a 3 tables
tblAgent, tblClient, tblVendor.
Since each entity requires different info to be stored, it makes sense to
have 3 different tables.
Now each agent, client or vendor can write many notes, so I created the
table like this to store all info
ContactID, ContactTypeID, NoteEnterDate, Note
where
ContactID will contain the id of either agent, client, or vendor (since id
will be the same data type for all 3 tables)
ContactTypeID 1 = Agent
ContactTypeID 2 = Client
ContactTypeID 3 = Vendor
So based on the ContactTypeID passed in to stored procedure as parameter, I
would know which table to join and contactID will be selective to filter
data. Of course there would be no referential integrity with ContactID
since it contains ID from 3 different tables. I am planning to have a
clustered index on ContactID, ContactTypeID (table will have no primary key)
Is this good design? Should I have separate note table for each Agent,
Client, and Vendor?
Can you think of any better design with regard to indexes?
Can you think of any better design to accomodate aforementioned scenario?
ThanksLooks good enough except that
contacttypeid,contactid,notedate (in that order can) be the primary key
clustered, if your notedate is not smalldatetime, of course.
--
"Justin" wrote:

> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary ke
y)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>
>|||If each row in this notes table will specifically apply to only one of the
other three tables, I would say create 3 seperate tables for notes. This
lets you define the referential integrity constraints and you will know that
all client notes are in one place, vendors in another, etc. Other than the
table structures being similar, is there any other reason why you would want
these all in one table?
"Justin" <jus820@.hotmail.com> wrote in message
news:O3WqCxebGHA.1208@.TK2MSFTNGP04.phx.gbl...
> I have a 3 tables
> tblAgent, tblClient, tblVendor.
> Since each entity requires different info to be stored, it makes sense to
> have 3 different tables.
> Now each agent, client or vendor can write many notes, so I created the
> table like this to store all info
> ContactID, ContactTypeID, NoteEnterDate, Note
> where
> ContactID will contain the id of either agent, client, or vendor (since id
> will be the same data type for all 3 tables)
> ContactTypeID 1 = Agent
> ContactTypeID 2 = Client
> ContactTypeID 3 = Vendor
> So based on the ContactTypeID passed in to stored procedure as parameter,
I
> would know which table to join and contactID will be selective to filter
> data. Of course there would be no referential integrity with ContactID
> since it contains ID from 3 different tables. I am planning to have a
> clustered index on ContactID, ContactTypeID (table will have no primary
key)
> Is this good design? Should I have separate note table for each Agent,
> Client, and Vendor?
> Can you think of any better design with regard to indexes?
> Can you think of any better design to accomodate aforementioned scenario?
> Thanks
>sql

Is This Feasable?

I am looking to design a system that will allow users to define their own
"objects". One of the methods I considered was to allow them to generate
tables representing these objects, and allow them to add and remove columns
from these tables as necessary.
These tables could potentially range from 3-50 columns and 1-10,000,000 rows
and there could be tens of thousands of new tables generated by users.
Is this feasable? What sort of hardware would be required to cope with such
a system (assuming thousands of simultaneous users)?Hi
Based on what you described, you are starting to talk enterprise class
hardware here.
8-16 way Intel Itanium2 Servers.
64GB+ RAM
Large SAN like EMC behind it.
And of course, clustered for high availability since it seems to be mission
critical.
Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
Short of knowing more about the application, it usage patters, transudations
per minute, not much more can be said.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mika" <mika@.hotmail.com> wrote in message
news:eOoKFHMoFHA.4012@.TK2MSFTNGP09.phx.gbl...
>I am looking to design a system that will allow users to define their own
>"objects". One of the methods I considered was to allow them to generate
>tables representing these objects, and allow them to add and remove columns
>from these tables as necessary.
> These tables could potentially range from 3-50 columns and 1-10,000,000
> rows and there could be tens of thousands of new tables generated by
> users.
> Is this feasable? What sort of hardware would be required to cope with
> such a system (assuming thousands of simultaneous users)?
>|||Allowing thousands of users to generate their own tables doesn't sound like
a way to build a scalable database capable of handling tens of millions of
rows of data. Not unless all your users are experienced database designers.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:_M-dnZ2dnZ00zAH8nZ2dnUuoYt-dnZ2dRVn-z52dnZ0@.giganews.com...
> Allowing thousands of users to generate their own tables doesn't sound
> like a way to build a scalable database capable of handling tens of
> millions of rows of data. Not unless all your users are experienced
> database designers.
Obviously the creation of the tables and columns will be tightly controlled
by the application.
What alternative technique would you suggest which would allow them to
simulate the creation of user-defined tables/objects?|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uAVS7TMoFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi
> Based on what you described, you are starting to talk enterprise class
> hardware here.
> 8-16 way Intel Itanium2 Servers.
> 64GB+ RAM
> Large SAN like EMC behind it.
> And of course, clustered for high availability since it seems to be
> mission critical.
> Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
> Short of knowing more about the application, it usage patters,
> transudations per minute, not much more can be said.
Hmmm thanks. Gives me an idea of what may be involved.|||Tables are not objects. The question you should be asking is how will you
represent RELATIONALLY the DATA that your users want to store. That has
nothing to do with the application creating its own tables for users. Since
you haven't told us anything about your data I can't answer your question.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sd6dnWPLCqA3pGLfRVn-qA@.giganews.com...
> Tables are not objects. The question you should be asking is how will you
> represent RELATIONALLY the DATA that your users want to store. That has
> nothing to do with the application creating its own tables for users.
> Since you haven't told us anything about your data I can't answer your
> question.
I realise tables aren't objects but I was considering using tables to
simulate objects.|||>> Is this feasable? <<
Where did you get the idea that a random user can design a schema on
the fly? Are you such a bad programmer that this is true'
No. The name of this design flaw is EAV and there are los of postings
about why it is a stupid, dangerous idea. It comes up over and over
again with newbies -- like Martingales in gambling.|||Conceptually a table might be regarded as representing a set of facts about
a particular class of objects but that analogy is a weak one because there
may not be a one-to-one correspondence between what users perceive as
"objects" and their efficient and logical representation as tables in an
RDBMS.
What I infer from your previous posts it that you don't know at design time
what data the users will wish to store. In most enterprise environments that
wouldn't generally be an acceptable starting point for a project. The first
step would be to research users' needs and identify a set of requirements
up-front. Without doing that it may be difficult to make a business case for
any kind of solution. Also, most enterprises entrust their data to database
professionals precisely because there is value in conforming information to
a standard data model. If users in an enterprise build their own models on
the fly then much of the data's value is lost.
Another possibility is that you are talking about providing some kind of
database hosting service to consumers, perhaps over the internet. In that
case I suggest that SQL Server may not even be the right tool for the job -
since it isn't obvious what kind of benefit you could expect to gain from
storing an unknown set of data relationally. Also, be aware that Microsoft
has a separate licensing model for Application Service Providers. I've no
idea if that will apply to you - I'm just guessing.
David Portas
SQL Server MVP
--|||>> Obviously the creation of the tables and columns will be tightly controll
ed by the application. <<
LOL! Anyone who can get to a tool like QA can destroy everything.
Over time you will have a lot of applications, and changes to existing
applications. Since each module and each tool does not have to have
the same integirty rules, they will drift. Since each of the users
will have a different data model, how would you write integrity
constraints anyway? Johnny has a rule that (age > 18) and Sally has
the rule that (age >= 18) and they are in the same Monster database.

Is This Feasable?

I am looking to design a system that will allow users to define their own
"objects". One of the methods I considered was to allow them to generate
tables representing these objects, and allow them to add and remove columns
from these tables as necessary.
These tables could potentially range from 3-50 columns and 1-10,000,000 rows
and there could be tens of thousands of new tables generated by users.
Is this feasable? What sort of hardware would be required to cope with such
a system (assuming thousands of simultaneous users)?Hi
Based on what you described, you are starting to talk enterprise class
hardware here.
8-16 way Intel Itanium2 Servers.
64GB+ RAM
Large SAN like EMC behind it.
And of course, clustered for high availability since it seems to be mission
critical.
Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
Short of knowing more about the application, it usage patters, transudations
per minute, not much more can be said.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mika" <mika@.hotmail.com> wrote in message
news:eOoKFHMoFHA.4012@.TK2MSFTNGP09.phx.gbl...
>I am looking to design a system that will allow users to define their own
>"objects". One of the methods I considered was to allow them to generate
>tables representing these objects, and allow them to add and remove columns
>from these tables as necessary.
> These tables could potentially range from 3-50 columns and 1-10,000,000
> rows and there could be tens of thousands of new tables generated by
> users.
> Is this feasable? What sort of hardware would be required to cope with
> such a system (assuming thousands of simultaneous users)?
>|||Allowing thousands of users to generate their own tables doesn't sound like
a way to build a scalable database capable of handling tens of millions of
rows of data. Not unless all your users are experienced database designers.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:_M-dnZ2dnZ00zAH8nZ2dnUuoYt-dnZ2dRVn-z52dnZ0@.giganews.com...
> Allowing thousands of users to generate their own tables doesn't sound
> like a way to build a scalable database capable of handling tens of
> millions of rows of data. Not unless all your users are experienced
> database designers.
Obviously the creation of the tables and columns will be tightly controlled
by the application.
What alternative technique would you suggest which would allow them to
simulate the creation of user-defined tables/objects?|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uAVS7TMoFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi
> Based on what you described, you are starting to talk enterprise class
> hardware here.
> 8-16 way Intel Itanium2 Servers.
> 64GB+ RAM
> Large SAN like EMC behind it.
> And of course, clustered for high availability since it seems to be
> mission critical.
> Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
> Short of knowing more about the application, it usage patters,
> transudations per minute, not much more can be said.
Hmmm thanks. Gives me an idea of what may be involved.|||Tables are not objects. The question you should be asking is how will you
represent RELATIONALLY the DATA that your users want to store. That has
nothing to do with the application creating its own tables for users. Since
you haven't told us anything about your data I can't answer your question.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sd6dnWPLCqA3pGLfRVn-qA@.giganews.com...
> Tables are not objects. The question you should be asking is how will you
> represent RELATIONALLY the DATA that your users want to store. That has
> nothing to do with the application creating its own tables for users.
> Since you haven't told us anything about your data I can't answer your
> question.
I realise tables aren't objects but I was considering using tables to
simulate objects.|||>> Is this feasable? <<
Where did you get the idea that a random user can design a schema on
the fly? Are you such a bad programmer that this is true'
No. The name of this design flaw is EAV and there are los of postings
about why it is a stupid, dangerous idea. It comes up over and over
again with newbies -- like Martingales in gambling.|||Conceptually a table might be regarded as representing a set of facts about
a particular class of objects but that analogy is a weak one because there
may not be a one-to-one correspondence between what users perceive as
"objects" and their efficient and logical representation as tables in an
RDBMS.
What I infer from your previous posts it that you don't know at design time
what data the users will wish to store. In most enterprise environments that
wouldn't generally be an acceptable starting point for a project. The first
step would be to research users' needs and identify a set of requirements
up-front. Without doing that it may be difficult to make a business case for
any kind of solution. Also, most enterprises entrust their data to database
professionals precisely because there is value in conforming information to
a standard data model. If users in an enterprise build their own models on
the fly then much of the data's value is lost.
Another possibility is that you are talking about providing some kind of
database hosting service to consumers, perhaps over the internet. In that
case I suggest that SQL Server may not even be the right tool for the job -
since it isn't obvious what kind of benefit you could expect to gain from
storing an unknown set of data relationally. Also, be aware that Microsoft
has a separate licensing model for Application Service Providers. I've no
idea if that will apply to you - I'm just guessing.
David Portas
SQL Server MVP
--|||>> Obviously the creation of the tables and columns will be tightly controll
ed by the application. <<
LOL! Anyone who can get to a tool like QA can destroy everything.
Over time you will have a lot of applications, and changes to existing
applications. Since each module and each tool does not have to have
the same integirty rules, they will drift. Since each of the users
will have a different data model, how would you write integrity
constraints anyway? Johnny has a rule that (age > 18) and Sally has
the rule that (age >= 18) and they are in the same Monster database.

Is This Feasable?

I am looking to design a system that will allow users to define their own
"objects". One of the methods I considered was to allow them to generate
tables representing these objects, and allow them to add and remove columns
from these tables as necessary.
These tables could potentially range from 3-50 columns and 1-10,000,000 rows
and there could be tens of thousands of new tables generated by users.
Is this feasable? What sort of hardware would be required to cope with such
a system (assuming thousands of simultaneous users)?
Hi
Based on what you described, you are starting to talk enterprise class
hardware here.
8-16 way Intel Itanium2 Servers.
64GB+ RAM
Large SAN like EMC behind it.
And of course, clustered for high availability since it seems to be mission
critical.
Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
Short of knowing more about the application, it usage patters, transudations
per minute, not much more can be said.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mika" <mika@.hotmail.com> wrote in message
news:eOoKFHMoFHA.4012@.TK2MSFTNGP09.phx.gbl...
>I am looking to design a system that will allow users to define their own
>"objects". One of the methods I considered was to allow them to generate
>tables representing these objects, and allow them to add and remove columns
>from these tables as necessary.
> These tables could potentially range from 3-50 columns and 1-10,000,000
> rows and there could be tens of thousands of new tables generated by
> users.
> Is this feasable? What sort of hardware would be required to cope with
> such a system (assuming thousands of simultaneous users)?
>
|||Allowing thousands of users to generate their own tables doesn't sound like
a way to build a scalable database capable of handling tens of millions of
rows of data. Not unless all your users are experienced database designers.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:_M-dnZ2dnZ00zAH8nZ2dnUuoYt-dnZ2dRVn-z52dnZ0@.giganews.com...
> Allowing thousands of users to generate their own tables doesn't sound
> like a way to build a scalable database capable of handling tens of
> millions of rows of data. Not unless all your users are experienced
> database designers.
Obviously the creation of the tables and columns will be tightly controlled
by the application.
What alternative technique would you suggest which would allow them to
simulate the creation of user-defined tables/objects?
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uAVS7TMoFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi
> Based on what you described, you are starting to talk enterprise class
> hardware here.
> 8-16 way Intel Itanium2 Servers.
> 64GB+ RAM
> Large SAN like EMC behind it.
> And of course, clustered for high availability since it seems to be
> mission critical.
> Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
> Short of knowing more about the application, it usage patters,
> transudations per minute, not much more can be said.
Hmmm thanks. Gives me an idea of what may be involved.
|||Tables are not objects. The question you should be asking is how will you
represent RELATIONALLY the DATA that your users want to store. That has
nothing to do with the application creating its own tables for users. Since
you haven't told us anything about your data I can't answer your question.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sd6dnWPLCqA3pGLfRVn-qA@.giganews.com...
> Tables are not objects. The question you should be asking is how will you
> represent RELATIONALLY the DATA that your users want to store. That has
> nothing to do with the application creating its own tables for users.
> Since you haven't told us anything about your data I can't answer your
> question.
I realise tables aren't objects but I was considering using tables to
simulate objects.
|||>> Is this feasable? <<
Where did you get the idea that a random user can design a schema on
the fly? Are you such a bad programmer that this is true?
No. The name of this design flaw is EAV and there are los of postings
about why it is a stupid, dangerous idea. It comes up over and over
again with newbies -- like Martingales in gambling.
|||Conceptually a table might be regarded as representing a set of facts about
a particular class of objects but that analogy is a weak one because there
may not be a one-to-one correspondence between what users perceive as
"objects" and their efficient and logical representation as tables in an
RDBMS.
What I infer from your previous posts it that you don't know at design time
what data the users will wish to store. In most enterprise environments that
wouldn't generally be an acceptable starting point for a project. The first
step would be to research users' needs and identify a set of requirements
up-front. Without doing that it may be difficult to make a business case for
any kind of solution. Also, most enterprises entrust their data to database
professionals precisely because there is value in conforming information to
a standard data model. If users in an enterprise build their own models on
the fly then much of the data's value is lost.
Another possibility is that you are talking about providing some kind of
database hosting service to consumers, perhaps over the internet. In that
case I suggest that SQL Server may not even be the right tool for the job -
since it isn't obvious what kind of benefit you could expect to gain from
storing an unknown set of data relationally. Also, be aware that Microsoft
has a separate licensing model for Application Service Providers. I've no
idea if that will apply to you - I'm just guessing.
David Portas
SQL Server MVP
|||>> Obviously the creation of the tables and columns will be tightly controlled by the application. <<
LOL! Anyone who can get to a tool like QA can destroy everything.
Over time you will have a lot of applications, and changes to existing
applications. Since each module and each tool does not have to have
the same integirty rules, they will drift. Since each of the users
will have a different data model, how would you write integrity
constraints anyway? Johnny has a rule that (age > 18) and Sally has
the rule that (age >= 18) and they are in the same Monster database.

Is This Feasable?

I am looking to design a system that will allow users to define their own
"objects". One of the methods I considered was to allow them to generate
tables representing these objects, and allow them to add and remove columns
from these tables as necessary.
These tables could potentially range from 3-50 columns and 1-10,000,000 rows
and there could be tens of thousands of new tables generated by users.
Is this feasable? What sort of hardware would be required to cope with such
a system (assuming thousands of simultaneous users)?Hi
Based on what you described, you are starting to talk enterprise class
hardware here.
8-16 way Intel Itanium2 Servers.
64GB+ RAM
Large SAN like EMC behind it.
And of course, clustered for high availability since it seems to be mission
critical.
Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
Short of knowing more about the application, it usage patters, transudations
per minute, not much more can be said.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mika" <mika@.hotmail.com> wrote in message
news:eOoKFHMoFHA.4012@.TK2MSFTNGP09.phx.gbl...
>I am looking to design a system that will allow users to define their own
>"objects". One of the methods I considered was to allow them to generate
>tables representing these objects, and allow them to add and remove columns
>from these tables as necessary.
> These tables could potentially range from 3-50 columns and 1-10,000,000
> rows and there could be tens of thousands of new tables generated by
> users.
> Is this feasable? What sort of hardware would be required to cope with
> such a system (assuming thousands of simultaneous users)?
>|||Allowing thousands of users to generate their own tables doesn't sound like
a way to build a scalable database capable of handling tens of millions of
rows of data. Not unless all your users are experienced database designers.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:_M-dnZ2dnZ00zAH8nZ2dnUuoYt-dnZ2dRVn-z52dnZ0@.giganews.com...
> Allowing thousands of users to generate their own tables doesn't sound
> like a way to build a scalable database capable of handling tens of
> millions of rows of data. Not unless all your users are experienced
> database designers.
Obviously the creation of the tables and columns will be tightly controlled
by the application.
What alternative technique would you suggest which would allow them to
simulate the creation of user-defined tables/objects?|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uAVS7TMoFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Hi
> Based on what you described, you are starting to talk enterprise class
> hardware here.
> 8-16 way Intel Itanium2 Servers.
> 64GB+ RAM
> Large SAN like EMC behind it.
> And of course, clustered for high availability since it seems to be
> mission critical.
> Unisys, Fujitsu, IBM and HP. Hope you have a big budget.
> Short of knowing more about the application, it usage patters,
> transudations per minute, not much more can be said.
Hmmm thanks. Gives me an idea of what may be involved.|||Tables are not objects. The question you should be asking is how will you
represent RELATIONALLY the DATA that your users want to store. That has
nothing to do with the application creating its own tables for users. Since
you haven't told us anything about your data I can't answer your question.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sd6dnWPLCqA3pGLfRVn-qA@.giganews.com...
> Tables are not objects. The question you should be asking is how will you
> represent RELATIONALLY the DATA that your users want to store. That has
> nothing to do with the application creating its own tables for users.
> Since you haven't told us anything about your data I can't answer your
> question.
I realise tables aren't objects but I was considering using tables to
simulate objects.|||>> Is this feasable? <<
Where did you get the idea that a random user can design a schema on
the fly? Are you such a bad programmer that this is true'
No. The name of this design flaw is EAV and there are los of postings
about why it is a stupid, dangerous idea. It comes up over and over
again with newbies -- like Martingales in gambling.|||Conceptually a table might be regarded as representing a set of facts about
a particular class of objects but that analogy is a weak one because there
may not be a one-to-one correspondence between what users perceive as
"objects" and their efficient and logical representation as tables in an
RDBMS.
What I infer from your previous posts it that you don't know at design time
what data the users will wish to store. In most enterprise environments that
wouldn't generally be an acceptable starting point for a project. The first
step would be to research users' needs and identify a set of requirements
up-front. Without doing that it may be difficult to make a business case for
any kind of solution. Also, most enterprises entrust their data to database
professionals precisely because there is value in conforming information to
a standard data model. If users in an enterprise build their own models on
the fly then much of the data's value is lost.
Another possibility is that you are talking about providing some kind of
database hosting service to consumers, perhaps over the internet. In that
case I suggest that SQL Server may not even be the right tool for the job -
since it isn't obvious what kind of benefit you could expect to gain from
storing an unknown set of data relationally. Also, be aware that Microsoft
has a separate licensing model for Application Service Providers. I've no
idea if that will apply to you - I'm just guessing.
--
David Portas
SQL Server MVP
--|||>> Obviously the creation of the tables and columns will be tightly controlled by the application. <<
LOL! Anyone who can get to a tool like QA can destroy everything.
Over time you will have a lot of applications, and changes to existing
applications. Since each module and each tool does not have to have
the same integirty rules, they will drift. Since each of the users
will have a different data model, how would you write integrity
constraints anyway? Johnny has a rule that (age > 18) and Sally has
the rule that (age >= 18) and they are in the same Monster database.sql

Is this design right?

I am modeling costs and revenues for Sales. I am having doubts about my approach.

SCENARIO:

We buy in most of our products and sell them on. Some products we make ourselves. Bought-in products can be sourced as items from various suppliers, with each supplier item being uniquely identified.

When analysing purchase costs, users want to drill down efficiently from Product Categories, through Products and down to the costs for the individual Supplier Items that we resell. A supplier item can be used for one sales product only.

Revenues can only ever be analysed at Category and Product level.

SOLUTION:

Tables as follows:

DIM_SupplierItem - contains all supplier items, together with product, category, and supplier groupings enabling hierarchies: Category-Product-SupplierItem and Supplier-SupplierItem.

DIM_Product - contains all products, also with category groupings, enabling the Category-Product hierarchy.

FACT_Costs - contains ProductKey, SupplierItemKey, Cost

FACT_Sales - contains ProductKey, Qty, Price

Prior to being loaded into to the warehouse, data is staged into a single product table, ensuring one source is used to populate the producy and category information included in both dimensions using the same keys and descriptions.

RATIONALE:

Providing a natural hierarchy within the Supplier Item dimension allows for simple, efficient drill down on costs. Ideally I would like to join the supplier item dimension to Sales measure group at the Product grain and do away with the Product dimension. But how can I? Because not all products have a supplier item, I would have sales in my Fact table with no corresponding product in the Supplier Item dimension. Only DIM_Product is guaranteed to include all products.

By including both a product key and a supplier item key on the Costs fact, I am allowing users to drill down to supplier items through products, whilst also supporting a conformed product dimension that allows users to compare sales and costs for products side-by-side when constraining using DIM_Product.

The conclusion is, I can't think of a better solution, but it feels wrong duplicating a subset of the categories and Products in DIM_Product into DIM_SupplierItem.

Any thoughts?

I think you are on the right track, but I'd suggest one change to your design...

For your DIM_SupplierItem dimension table, consider the DIM_Product table to be a part of a snowflaked design. Thus, instead of duplicating product and category information in the DIM_SupplierItem table, just include a foreign key to the DIM_Product dimension table. When you construct your SupplierItem dimension in SSAS, just use both tables as the source for the dimension and construct the attributes and hierarchies as you would if the product and category information was all contained within a single table. Then, just create your Product dimension using the DIM_Product table as you are already planning on doing.

When you create your cube, you should then be able to add your Costs measure group and relate it to SupplierItem and Product dimensions as planned. And add your Sales measure group and relate it to the Product dimension as planned.

This will keep you from duplicating the product and category information across two different tables in your star-schema database. It will, however, still duplicate the data within the two different dimension structures in SSAS, but that is typically not considered as much of an issue as duplicating the data in the star-schema is...

HTH,

Dave Fackler

Is this Correct database design

HI all,
I have a claims database, which has a header table, details table and
otherinfo table. The header table will contain claim header info, ie
claimnumber date of loss etc., the details would contain claim detail, as
details of loss, damage ammounts, etc, the otherinfo contains information
not common to all classes of claims, so I would have a motor table
conmtaining driver, motor type model etc, GPA, would contain empoyee name,
earnings medical etc.
Now this is just in pricipal, hence no ddl, but anyway, some one suggested
that for the otherinfo table I create a descriptor table that will enable us
to add numerous columns for this "otherinfo" information at will
his quote is:
"descriptor table that can hold the field names, the data type, an input
string regular expression, and a bit field for required or not. Field Start
Date, and Field End Date. Fields can then be added and removed at will"
Whyle this might sound good, my gut says its a bad idea, for starters, it
could be a nightmare trying to create stable reports, etc.
Any other thought here or suggestions
Thanks
RobertYour gut feeling is correct; this is generally a bad idea. SQL
databases should be used for strongly-typed and stable schemas; in
other words, the designer should know what the information is going to
look like before you develop it. Trying to dynamically construct
tables from columns and column values added at will is a performance
nightmare. If a column is needed for reporting purposes (or some other
application purpose) then alter your design.
That being said, there are other database engines that MAY do what you
require, such as object-oriented databases; I've never worked with
them, so I don't know for sure. Yo may also explore other options for
holding miscellaneous information (such as the xml datatype in SQL
2005). However, what your friend is suggesting is NOT a relational
design, and therfore should not be used in an RDBMS solution.
HTH,
Stusql

Is this bad design?

Hey,

If you have a one to many relationship, say a product has many reviews, is it good design to have a flag in the product which indicates whether there are any review in the reviews table?

One might do this so that in a stored procedure one can check to see if there are any product reviews before running a select query on the reviews table. If there aren't then you've saved a select query.

Also, this flag could be used to determine the type of formating required for the data. I suppose it would be quite easy to just look up the count of reviews in the data table to gather the same info.

Cheers,

I.I hate designs like that. That sp is still going to have to query the product table to check the flag, so you haven't gained a thing. A simple Count Where fk=pk will tell you just as fast, or just do the query--you're going to want the records anyway.

The only way I could see this helping would be if ALL of the following conditions were met:
** This is an extremely active database that is being pushed very hard
** It is rare for there to be any reviews
** You already have the product table row

But what a headache to try to keep that flag updated. Maybe a trigger could be used -- add a record, increment the count, delete a record, decrement the count.|||It didn't feel right either.

I think another problem with it is that if a sp determines that there are reviews to gather and then a concurrent user goes and deletes all the reviews, the first sp will return no reviews so the formatting will be set up to show reviews but they'll be none.

"That sp is still going to have to query the product table to check the flag, so you haven't gained a thing."

If you had several flags for several tables then it might save a few selects. You could get all the flags in a single select.

Is this an ok design?

Hey im really confused about this database design ive encountered, and I
wonder if anyone can help me understqand if this is a decent design and what
to do about it. The database contains about 24 billion records. It is split
into a distributed partitioned view on a RAID 5 of 7 different harddrives.
There are no columns that are unique...the closest one is a datetime but
there can be up to 10 records with the same date time...the rest of the
columns can expect to have a few billion dupicates. There is this bizarre
thing...a timestamp column, and the database is clustered on the timestamp
and the view uses a check constraint on that. It is not a unique index. I
understand that timestamp will change whenever any data is modified so I don
t
understand what effect this might have overall. Any thoughts on this?
Message posted via http://www.droptable.comCharlesT via droptable.com wrote:

> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and wh
at
> to do about it. The database contains about 24 billion records. It is spli
t
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I d
ont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
How is the data used? There may be a case for temporarily staging data
like this - if you have an external data feed over which you don't have
direct control for example - but to store data permanently in this form
has to be insanely inefficient. According to your narrative upto 90% of
the data is redundant, which is a heavy price to pay at 24 billion
rows!
Clustering on a TIMESTAMP column makes no sense that I can see. Doing
so will create an index hot-spot and TIMESTAMP is anyway not normally
used in queries without other key columns.
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
--|||Hi
Each table in SQL Serverc must have a PRIMERY KEY. Read about it for more
details in the BOL.
What does it give you , having a CI on timestamp column?
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com|||Are you certain that the clustered index/partitioning column is a timestamp
column? I agree that a timestamp *data type* column would make a very poor
choice for the clustered index. However, a clustered index on a
sequentially increasing value (e.g. CreateDate datetime value or identity
column) can greatly improve insert performance for large tables because I/O
is reduced by 'appending' to the end of the table.
For example, I had a single table containing 2-3 billion rows. The PK index
was clustered and included an increasing datetime column as the high-order
column. Insert performance was excellent as were deletes (based on data
age). Queries always specified a datetime range and performance was
directly proportional to number of rows in the range.
I believe you would get similar performance (perhaps even better) with this
type of index design in a DPV, even if the clustered index is not the PK. A
lot depends on the nature of your queries.
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com|||On Sun, 05 Feb 2006 08:44:54 GMT, CharlesT via droptable.com wrote:

>Hey im really confused about this database design ive encountered, and I
>wonder if anyone can help me understqand if this is a decent design and wha
t
>to do about it. The database contains about 24 billion records. It is split
>into a distributed partitioned view on a RAID 5 of 7 different harddrives.
>There are no columns that are unique...the closest one is a datetime but
>there can be up to 10 records with the same date time...the rest of the
>columns can expect to have a few billion dupicates. There is this bizarre
>thing...a timestamp column, and the database is clustered on the timestamp
>and the view uses a check constraint on that. It is not a unique index. I
>understand that timestamp will change whenever any data is modified so I do
nt
>understand what effect this might have overall. Any thoughts on this?
Hi Charles,
The datatype name "timestamp" is my candidate for the most ill-chosen
name of the last century. :-)
Just to clarify: are you talking about a column that is used to keep a
true timestamp (i.e. the time that something happened), is maybe even
called timestamp but has a datetime datatype? Or are you talking about a
column with a "timestamp" (equivalent to "rowversion") datatype, used to
help in optimistic locking scenario's?
In the first case, it might not be that bad at all - especially if it is
a timestamp that will never change (i.e. the moment a row was entered in
the database). From your message, I'm afraid you'll still have other
issues with your design, but this wouldn't be one of them.
In the latter case, I can only advise you to change this ASAP. Or run
away screaming - your choice <g>
Hugo Kornelis, SQL Server MVP|||yeah this table has no primary key.
its call logs imported from another server.
Sometimes the index just doesnt work and it can take hours to run a normal
query.
There is no natural candidate for a primary key and i am told that there is
no reason to have a surrogate key because of the redundancy of data. I am
also told an index is pointless for the same reason. Queries are typically o
f
the form how many calls with this attribute where made in a certain month. I
t
seems like date could be a candidate for an index then, but its not there. I
m
not sure if the person who designed the database knew that timestamp and
datetime are very different.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200602/1|||> yeah this table has no primary key.
Your view is not a true distributed partitioned view if you have no primary
key. In a DPV, you must have a primary key, partition based a column that
is part of the PK and create a check constraint on the partitioning column
that enforces how data are partitioned. This allows SQL Server to eliminate
unneeded tables during query optimization. With a regular UNION ALL view
(non-DPV), all of the underlying tables need to be accessed during queries.
Although you might not have a single column key, I would expect that you
might have a composite key uniquely identifies a row. For example,
CallDateTime, OriginatingNumber and TerminatingNumber can uniquely identify
a call event. If not, then you'll need to introduce a surrogate key to
address dirty data (assuming data scrubbing isn't an option).

> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
I would expect all queries against this view to take hours unless a
timestamp value or narrow range is specified.
You mentioned in your original post that the timestamp column has a check
constraint on it. Can you post the complete DDL for one of the tables?
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b792579afb4f@.uwe...
> yeah this table has no primary key.
> its call logs imported from another server.
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
> There is no natural candidate for a primary key and i am told that there
> is
> no reason to have a surrogate key because of the redundancy of data. I am
> also told an index is pointless for the same reason. Queries are typically
> of
> the form how many calls with this attribute where made in a certain month.
> It
> seems like date could be a candidate for an index then, but its not there.
> Im
> not sure if the person who designed the database knew that timestamp and
> datetime are very different.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200602/1sql

Is this an ok design?

Hey im really confused about this database design ive encountered, and I
wonder if anyone can help me understqand if this is a decent design and what
to do about it. The database contains about 24 billion records. It is split
into a distributed partitioned view on a RAID 5 of 7 different harddrives.
There are no columns that are unique...the closest one is a datetime but
there can be up to 10 records with the same date time...the rest of the
columns can expect to have a few billion dupicates. There is this bizarre
thing...a timestamp column, and the database is clustered on the timestamp
and the view uses a check constraint on that. It is not a unique index. I
understand that timestamp will change whenever any data is modified so I dont
understand what effect this might have overall. Any thoughts on this?
Message posted via http://www.droptable.com
CharlesT via droptable.com wrote:

> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and what
> to do about it. The database contains about 24 billion records. It is split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
How is the data used? There may be a case for temporarily staging data
like this - if you have an external data feed over which you don't have
direct control for example - but to store data permanently in this form
has to be insanely inefficient. According to your narrative upto 90% of
the data is redundant, which is a heavy price to pay at 24 billion
rows!
Clustering on a TIMESTAMP column makes no sense that I can see. Doing
so will create an index hot-spot and TIMESTAMP is anyway not normally
used in queries without other key columns.
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
|||Hi
Each table in SQL Serverc must have a PRIMERY KEY. Read about it for more
details in the BOL.
What does it give you , having a CI on timestamp column?
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
|||Are you certain that the clustered index/partitioning column is a timestamp
column? I agree that a timestamp *data type* column would make a very poor
choice for the clustered index. However, a clustered index on a
sequentially increasing value (e.g. CreateDate datetime value or identity
column) can greatly improve insert performance for large tables because I/O
is reduced by 'appending' to the end of the table.
For example, I had a single table containing 2-3 billion rows. The PK index
was clustered and included an increasing datetime column as the high-order
column. Insert performance was excellent as were deletes (based on data
age). Queries always specified a datetime range and performance was
directly proportional to number of rows in the range.
I believe you would get similar performance (perhaps even better) with this
type of index design in a DPV, even if the clustered index is not the PK. A
lot depends on the nature of your queries.
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.droptable.com
|||On Sun, 05 Feb 2006 08:44:54 GMT, CharlesT via droptable.com wrote:

>Hey im really confused about this database design ive encountered, and I
>wonder if anyone can help me understqand if this is a decent design and what
>to do about it. The database contains about 24 billion records. It is split
>into a distributed partitioned view on a RAID 5 of 7 different harddrives.
>There are no columns that are unique...the closest one is a datetime but
>there can be up to 10 records with the same date time...the rest of the
>columns can expect to have a few billion dupicates. There is this bizarre
>thing...a timestamp column, and the database is clustered on the timestamp
>and the view uses a check constraint on that. It is not a unique index. I
>understand that timestamp will change whenever any data is modified so I dont
>understand what effect this might have overall. Any thoughts on this?
Hi Charles,
The datatype name "timestamp" is my candidate for the most ill-chosen
name of the last century. :-)
Just to clarify: are you talking about a column that is used to keep a
true timestamp (i.e. the time that something happened), is maybe even
called timestamp but has a datetime datatype? Or are you talking about a
column with a "timestamp" (equivalent to "rowversion") datatype, used to
help in optimistic locking scenario's?
In the first case, it might not be that bad at all - especially if it is
a timestamp that will never change (i.e. the moment a row was entered in
the database). From your message, I'm afraid you'll still have other
issues with your design, but this wouldn't be one of them.
In the latter case, I can only advise you to change this ASAP. Or run
away screaming - your choice <g>
Hugo Kornelis, SQL Server MVP
|||yeah this table has no primary key.
its call logs imported from another server.
Sometimes the index just doesnt work and it can take hours to run a normal
query.
There is no natural candidate for a primary key and i am told that there is
no reason to have a surrogate key because of the redundancy of data. I am
also told an index is pointless for the same reason. Queries are typically of
the form how many calls with this attribute where made in a certain month. It
seems like date could be a candidate for an index then, but its not there. Im
not sure if the person who designed the database knew that timestamp and
datetime are very different.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200602/1
|||> yeah this table has no primary key.
Your view is not a true distributed partitioned view if you have no primary
key. In a DPV, you must have a primary key, partition based a column that
is part of the PK and create a check constraint on the partitioning column
that enforces how data are partitioned. This allows SQL Server to eliminate
unneeded tables during query optimization. With a regular UNION ALL view
(non-DPV), all of the underlying tables need to be accessed during queries.
Although you might not have a single column key, I would expect that you
might have a composite key uniquely identifies a row. For example,
CallDateTime, OriginatingNumber and TerminatingNumber can uniquely identify
a call event. If not, then you'll need to introduce a surrogate key to
address dirty data (assuming data scrubbing isn't an option).

> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
I would expect all queries against this view to take hours unless a
timestamp value or narrow range is specified.
You mentioned in your original post that the timestamp column has a check
constraint on it. Can you post the complete DDL for one of the tables?
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via droptable.com" <u18248@.uwe> wrote in message
news:5b792579afb4f@.uwe...
> yeah this table has no primary key.
> its call logs imported from another server.
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
> There is no natural candidate for a primary key and i am told that there
> is
> no reason to have a surrogate key because of the redundancy of data. I am
> also told an index is pointless for the same reason. Queries are typically
> of
> the form how many calls with this attribute where made in a certain month.
> It
> seems like date could be a candidate for an index then, but its not there.
> Im
> not sure if the person who designed the database knew that timestamp and
> datetime are very different.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200602/1

Is this an ok design?

Hey im really confused about this database design ive encountered, and I
wonder if anyone can help me understqand if this is a decent design and what
to do about it. The database contains about 24 billion records. It is split
into a distributed partitioned view on a RAID 5 of 7 different harddrives.
There are no columns that are unique...the closest one is a datetime but
there can be up to 10 records with the same date time...the rest of the
columns can expect to have a few billion dupicates. There is this bizarre
thing...a timestamp column, and the database is clustered on the timestamp
and the view uses a check constraint on that. It is not a unique index. I
understand that timestamp will change whenever any data is modified so I dont
understand what effect this might have overall. Any thoughts on this?
--
Message posted via http://www.sqlmonster.comCharlesT via SQLMonster.com wrote:
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and what
> to do about it. The database contains about 24 billion records. It is split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.sqlmonster.com
How is the data used? There may be a case for temporarily staging data
like this - if you have an external data feed over which you don't have
direct control for example - but to store data permanently in this form
has to be insanely inefficient. According to your narrative upto 90% of
the data is redundant, which is a heavy price to pay at 24 billion
rows!
Clustering on a TIMESTAMP column makes no sense that I can see. Doing
so will create an index hot-spot and TIMESTAMP is anyway not normally
used in queries without other key columns.
--
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
--|||Hi
Each table in SQL Serverc must have a PRIMERY KEY. Read about it for more
details in the BOL.
What does it give you , having a CI on timestamp column?
"CharlesT via SQLMonster.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.sqlmonster.com|||Are you certain that the clustered index/partitioning column is a timestamp
column? I agree that a timestamp *data type* column would make a very poor
choice for the clustered index. However, a clustered index on a
sequentially increasing value (e.g. CreateDate datetime value or identity
column) can greatly improve insert performance for large tables because I/O
is reduced by 'appending' to the end of the table.
For example, I had a single table containing 2-3 billion rows. The PK index
was clustered and included an increasing datetime column as the high-order
column. Insert performance was excellent as were deletes (based on data
age). Queries always specified a datetime range and performance was
directly proportional to number of rows in the range.
I believe you would get similar performance (perhaps even better) with this
type of index design in a DPV, even if the clustered index is not the PK. A
lot depends on the nature of your queries.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via SQLMonster.com" <u18248@.uwe> wrote in message
news:5b6aaa612bed9@.uwe...
> Hey im really confused about this database design ive encountered, and I
> wonder if anyone can help me understqand if this is a decent design and
> what
> to do about it. The database contains about 24 billion records. It is
> split
> into a distributed partitioned view on a RAID 5 of 7 different harddrives.
> There are no columns that are unique...the closest one is a datetime but
> there can be up to 10 records with the same date time...the rest of the
> columns can expect to have a few billion dupicates. There is this bizarre
> thing...a timestamp column, and the database is clustered on the timestamp
> and the view uses a check constraint on that. It is not a unique index. I
> understand that timestamp will change whenever any data is modified so I
> dont
> understand what effect this might have overall. Any thoughts on this?
> --
> Message posted via http://www.sqlmonster.com|||On Sun, 05 Feb 2006 08:44:54 GMT, CharlesT via SQLMonster.com wrote:
>Hey im really confused about this database design ive encountered, and I
>wonder if anyone can help me understqand if this is a decent design and what
>to do about it. The database contains about 24 billion records. It is split
>into a distributed partitioned view on a RAID 5 of 7 different harddrives.
>There are no columns that are unique...the closest one is a datetime but
>there can be up to 10 records with the same date time...the rest of the
>columns can expect to have a few billion dupicates. There is this bizarre
>thing...a timestamp column, and the database is clustered on the timestamp
>and the view uses a check constraint on that. It is not a unique index. I
>understand that timestamp will change whenever any data is modified so I dont
>understand what effect this might have overall. Any thoughts on this?
Hi Charles,
The datatype name "timestamp" is my candidate for the most ill-chosen
name of the last century. :-)
Just to clarify: are you talking about a column that is used to keep a
true timestamp (i.e. the time that something happened), is maybe even
called timestamp but has a datetime datatype? Or are you talking about a
column with a "timestamp" (equivalent to "rowversion") datatype, used to
help in optimistic locking scenario's?
In the first case, it might not be that bad at all - especially if it is
a timestamp that will never change (i.e. the moment a row was entered in
the database). From your message, I'm afraid you'll still have other
issues with your design, but this wouldn't be one of them.
In the latter case, I can only advise you to change this ASAP. Or run
away screaming - your choice <g>
--
Hugo Kornelis, SQL Server MVP|||yeah this table has no primary key.
its call logs imported from another server.
Sometimes the index just doesnt work and it can take hours to run a normal
query.
There is no natural candidate for a primary key and i am told that there is
no reason to have a surrogate key because of the redundancy of data. I am
also told an index is pointless for the same reason. Queries are typically of
the form how many calls with this attribute where made in a certain month. It
seems like date could be a candidate for an index then, but its not there. Im
not sure if the person who designed the database knew that timestamp and
datetime are very different.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1|||> yeah this table has no primary key.
Your view is not a true distributed partitioned view if you have no primary
key. In a DPV, you must have a primary key, partition based a column that
is part of the PK and create a check constraint on the partitioning column
that enforces how data are partitioned. This allows SQL Server to eliminate
unneeded tables during query optimization. With a regular UNION ALL view
(non-DPV), all of the underlying tables need to be accessed during queries.
Although you might not have a single column key, I would expect that you
might have a composite key uniquely identifies a row. For example,
CallDateTime, OriginatingNumber and TerminatingNumber can uniquely identify
a call event. If not, then you'll need to introduce a surrogate key to
address dirty data (assuming data scrubbing isn't an option).
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
I would expect all queries against this view to take hours unless a
timestamp value or narrow range is specified.
You mentioned in your original post that the timestamp column has a check
constraint on it. Can you post the complete DDL for one of the tables?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CharlesT via SQLMonster.com" <u18248@.uwe> wrote in message
news:5b792579afb4f@.uwe...
> yeah this table has no primary key.
> its call logs imported from another server.
> Sometimes the index just doesnt work and it can take hours to run a normal
> query.
> There is no natural candidate for a primary key and i am told that there
> is
> no reason to have a surrogate key because of the redundancy of data. I am
> also told an index is pointless for the same reason. Queries are typically
> of
> the form how many calls with this attribute where made in a certain month.
> It
> seems like date could be a candidate for an index then, but its not there.
> Im
> not sure if the person who designed the database knew that timestamp and
> datetime are very different.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1

Is this a sound design for Reporting Services

Hi,
We are looking at deploying Report Services within our organisation
however, we have to support multiple clients, operating systems and
authentication methods, so we are unable to use the in-built Windows
Security or Reporting Services Portal.
To resolve this issue I have designed a WebService that acts as a
facade / proxy to the Reporting Services Web Service and uses a
specifically created Windows Account to authenticate against the Report
Server.
Then the various consumers of our reports (home grown sites plus third
party portals) request the reports from our Web Service which supports
the various authentication methods required (LDAP / Single Sign-on etc)
and manages the various Roles / Permissions for the reports.
The Report Server is then securely tied down to the single Windows
Account that is used by our web service.
Does the above sound like a good solution or has anyone else used a
similar approach?
Thanks
RussPersonally, I am an advocate against building any additional facades on top
of RS. The moment you introduce another "wrapper", you are pretty much
kissing good-bye perhaps 30% of the RS feature set. For example, you can't
use the RS 2005 report viewers, interactive features, etc., plus in many
cases you will find yourself just reinventing the wheel (e.g. to handle
parameters, printing, etc.) Instead, in your case, I would gravitate toward
replacing the default Windows-based authentication with custom
authentication. If your reporting clients can invoke programatically your
web service, they should be able to capture the authentication cookie and
pass it back.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"russ" <russ.1@.excite.com> wrote in message
news:1132222181.477746.69640@.g44g2000cwa.googlegroups.com...
> Hi,
> We are looking at deploying Report Services within our organisation
> however, we have to support multiple clients, operating systems and
> authentication methods, so we are unable to use the in-built Windows
> Security or Reporting Services Portal.
> To resolve this issue I have designed a WebService that acts as a
> facade / proxy to the Reporting Services Web Service and uses a
> specifically created Windows Account to authenticate against the Report
> Server.
> Then the various consumers of our reports (home grown sites plus third
> party portals) request the reports from our Web Service which supports
> the various authentication methods required (LDAP / Single Sign-on etc)
> and manages the various Roles / Permissions for the reports.
> The Report Server is then securely tied down to the single Windows
> Account that is used by our web service.
> Does the above sound like a good solution or has anyone else used a
> similar approach?
> Thanks
> Russ
>|||Hi Teo,
Thanks for the reply. I should have mentioned that we are using 2000
and not 2005 and therefore are unable to use the RS feature set due to
the poor non-IE support.
Cheers
Russ|||Nonetheless, IMO, one should view the RS web service as the ultimate facade
and avoid building additional facades on top of it unless it is absolutely
necessary. There are functionality, security, deployment, and maintenance
tradeoffs once you go for the "facade-on-top-of-facade" approach.
BTW, you can still use URL addressability with non-web clients to get the
full RS 2000 feature set. For example, a COM or .NET application can use the
Web Browser control. That said, given the fact that URL addressability is
deprecated in RS 2005 in favor of SOAP, I would strongly consider moving
forward with RS 2005 and the Report Viewer control if possible.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"russ" <russ.1@.excite.com> wrote in message
news:1132238241.374215.84200@.g14g2000cwa.googlegroups.com...
> Hi Teo,
> Thanks for the reply. I should have mentioned that we are using 2000
> and not 2005 and therefore are unable to use the RS feature set due to
> the poor non-IE support.
> Cheers
> Russ
>|||many thanks for the advice :)
I think a push for 2005 is in ordersql

Is this a poor database design?

Setup
I am using a simple aspx page to calculate a person's tax filing status and capturing statistical information. Most of the items are drop down boxes or radio buttons but a few are checkboxes.

Question
My plan is to give each option in the check boxes its own column in the database this will lead to a table that is 70 columns wide. Is that to many? Most of the info will be very small like "yes" or "no". I will be using a stored procedure to insert the info. This will be coming down a VPN

Goal
I would like to give the admin staff here real time access to the data via link tables and such. I would like there to be only one table so it will be easy for them to pars the info quickly without needing to join tables or write unions. Ideally this would get me out of the business of creating one off reports.I would recomend a more normalized table design.

If your admin users want to view the data de-normailzed you can create a view which accomplishes this.|||Go to the SQL Server Web site and look for the SQL Server 2000 AdventureWorks, it will get you create table statements that is close to what you are looking for because I takes table Reationship to Upper and Lower bound Cardinality. The only human interpretation of Peter Chen ERD relational Algebra. Hope this helps.

Kind regards,
Gift Peddie

Is this a bad database design?

I have a DVD database. I have several categories (three) that a DVD
may be in.
I put all three possible topic fields in the main table that holds the
title, etc. of the DVD.
For example, I have:
title, when_made, running_time, genre, subject, specific_subject
the last three are the three types a DVD may be.
My question is: Should I put the genre and subtypes all in that main
table or should I have related them in some way to the main table?
Thanks for any help.There should be a Genre table with a primary key and the Genre name. Then,
you set up a foreign key from the DVD table to the Genre table. If there
can be more than one genre per DVD, then you'd need to have a third table -
known as a link table or associative object - in order to resolve the M:M
relationship. In that case, there would be 2 foreign keys from it - one to
the Genre and another to the DVD. There would be no FK from DVD to Genre.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<needin4mation@.gmail.com> wrote in message
news:1128428580.337372.277460@.g43g2000cwa.googlegroups.com...
I have a DVD database. I have several categories (three) that a DVD
may be in.
I put all three possible topic fields in the main table that holds the
title, etc. of the DVD.
For example, I have:
title, when_made, running_time, genre, subject, specific_subject
the last three are the three types a DVD may be.
My question is: Should I put the genre and subtypes all in that main
table or should I have related them in some way to the main table?
Thanks for any help.|||I keep the foreign keys in the main table. I have three other tables:
genre, subgenre, and subsubgenre (okay they aren't really called
subsub, but you get the drift). Each genre and sub has a key in the
main table. I don't save the wording, just the key. I was thinking
that having main genre, subgenre, and subsubgenre like that, all three,
in the same main table (even though keyed) was "wrong."|||Then it sounds like all you need in the DVD table is the subsubgenre, as far
as FK's are concerned. The subsubgenre should have a FK to the subgenre
table, which in turn has a FK to the genre table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<needin4mation@.gmail.com> wrote in message
news:1128439915.363058.318680@.z14g2000cwz.googlegroups.com...
I keep the foreign keys in the main table. I have three other tables:
genre, subgenre, and subsubgenre (okay they aren't really called
subsub, but you get the drift). Each genre and sub has a key in the
main table. I don't save the wording, just the key. I was thinking
that having main genre, subgenre, and subsubgenre like that, all three,
in the same main table (even though keyed) was "wrong."|||To be honest, it sounds like you need a many-to-many relationship
dvds - dvdGenres - Genres
This way you can assign multiple Genres to a single dvd without limiting
yourself to X number of them.
<needin4mation@.gmail.com> wrote in message
news:1128439915.363058.318680@.z14g2000cwz.googlegroups.com...
> I keep the foreign keys in the main table. I have three other tables:
> genre, subgenre, and subsubgenre (okay they aren't really called
> subsub, but you get the drift). Each genre and sub has a key in the
> main table. I don't save the wording, just the key. I was thinking
> that having main genre, subgenre, and subsubgenre like that, all three,
> in the same main table (even though keyed) was "wrong."
>|||>> Should I put the genre and subtypes all in that main table or should I
If they are distinct entity types, you must represent them in separate
tables. Based on your narratives :
CREATE TABLE Genre ( genre PK, ... )
CREATE TABLE Subjects ( subject PK, genre FK, ... )
CREATE TABLE Specifics ( spec_subject PK, subject FK, ... )
CREATE TABLE Movies (
movie_id PK, title, production_date, running_time, spec_subject FK,
UNIQUE( ... ), CHECK ( ... ), )
A seemingly easy & common, but misguided design is to cram all of them up in
a single table with NULL-able columns.
Anith|||I understood up until you had spec_subject FK in the Movies table. I
would have thought that genre went there. Do I misunderstand?
I actually have this design (except for the movies part), but was going
to use it for a drop down list. If for example a person selects
"Horror" they would not have the subject "kids under three movie"
(hopefully) and therefore should not have that in the selection.
In the main table that actually holds the data, I had the three columns
and insert a genre, subgenre and subsubwhatever in the table. The
values were limited at the data entry.
But what I think you are all saying is that if I have a single column
for genre that that would have a foreign key in subjects and that when
I retrieved my records it would pull that movie, that single genre and
then all subjects under that genre. Somewhere someone would have to,
of course, type in what subject was under a certain genre, but that is
okay.
On the other hand I'm not sure if that works because a Genre may imply
many subjects, but not all subjects apply to the save movie. It may be
comedy and have slapstick, black comedy, and so on, but the movie
itself may only be comedy and slapstick.
So how would I store this? Are you saying that in my table that I
would have:
movie - movieid, title...
genre - genreid, genre_verbiage
subject - subjectid, genreid, subject_verbiage
specific - specificid, subjectid, genreid, specific_verbiage
Is this why are saying to have the spec_subject FK in the master (one
side) table? so that when retrieve the data it would be like this:
select * from movie, specific, subject, genre
where
movie.spec_subject = specific.spec_subject
and
specific.subject_id = subject.subject_id
and
subject.genreid = genre.genre_id
Sorry for the long post. Just trying to understand. I inherited the
database.|||In general, newsgroups are not a great place for design advice since your
conceptual model & business rules are mostly transparent to others here.
Miscommunication and misinterpretations are common and the suggestions one
receives here are based on what others perceive as the problem and could
possibly be wrong with regard to the actual problem.
My lack of familiarity to your conceptual model and business rules might
have contributed to the misunderstanding. Your initial post gave me the
impression that specific subjects belong to subjects and subjects in turn
belong to genres. In other words, in my mind, a movie falls under one
specific subject, which in turn belonged to one main subject that belonged
to a single genre.
Before thinking about client side interface controls, let us consider the
actual entities, their attributes and the relationship among them. In your
case, is there a relationship between subjects and genre? Is there a
relationship between specifics and subjects? Can you post some examples for
each?
As a general recommendation, integrity constraints should be applied at the
database as well rather than only at the data entry interface.
Here is the categorization at blockbuster online: http://tinyurl.com/8rgz9 .
Do you have something similar? If not, post some examples for genre,
subject, specific_subject etc.
The general design rules of thumb are:
* When you have a 1-to-1 relationship between two entity types, unless there
are any non-de preserving relationships, you may represent them in a single
table.
* When you have a m-to-1 relationship between two entity types, you should
use a referential integrity constraint ( FK ) between the tables
representing these entity types
* When you have an m-to-n relationship between two or more entity types, you
should introduce an "association" table which reduces the schema to two or
more many-to-one relationships on each table representing these entity
types.
Anith|||>> I was thinking that having main genre, subgenre, and subsubgenre like tha
t, all three, in the same main table (even though keyed) was "wrong." <<
No, not if they are really different attributes. However, I would
prefer to design a hierachical encoding like Dewey Decimal for this
kind of thing.

Is there the equivalent of the Access Query Design Grid in SQL Server?

Hi. Is there the equivalent of the Access Query Design Grid in SQL
Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
Grid? If so, does it design queries for XML data? Also if so, I read
somewhere on the web that the Access Query Design Grid doesn't do joins
so would I be correct to assume the SQL Server version wouldn't do
joins either?
Thank you. Regards.
There is no XQuery designer unfortunately.
Sorry
Michael
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133931766.736605.160010@.z14g2000cwz.googlegr oups.com...
> Hi. Is there the equivalent of the Access Query Design Grid in SQL
> Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
> Grid? If so, does it design queries for XML data? Also if so, I read
> somewhere on the web that the Access Query Design Grid doesn't do joins
> so would I be correct to assume the SQL Server version wouldn't do
> joins either?
> Thank you. Regards.
>

Is there the equivalent of the Access Query Design Grid in SQL Server?

Hi. Is there the equivalent of the Access Query Design Grid in SQL
Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
Grid? If so, does it design queries for XML data? Also if so, I read
somewhere on the web that the Access Query Design Grid doesn't do joins
so would I be correct to assume the SQL Server version wouldn't do
joins either?
Thank you. Regards.There is no XQuery designer unfortunately.
Sorry
Michael
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133931766.736605.160010@.z14g2000cwz.googlegroups.com...
> Hi. Is there the equivalent of the Access Query Design Grid in SQL
> Server 2005 beta? In other words, a SQL Server 2005 beta Query Design
> Grid? If so, does it design queries for XML data? Also if so, I read
> somewhere on the web that the Access Query Design Grid doesn't do joins
> so would I be correct to assume the SQL Server version wouldn't do
> joins either?
> Thank you. Regards.
>

Is there anyway to set a partition so that it cannot be re-processed?

As part of my design, I plan to have partitions holding historical data. Since the underlying data will have been deleted from the underlying DW, I want to ensure that the partition cannot be re-processed.

Does anyone know if this can be done and, if so, how?

Ta

Dirc

No this cannot be done. Also, please be advised, that your decision to have AS to be a primary data source is not a scenario that AS was designed for. Data Warehouse should be a primary data source. If you absolutely cannot keep data there, you need to have good backup strategy to be able to restore your AS partition data if something happens to it.|||

Sadly, the decision as to the removal of data from the DW is not in my hands. It's initial intention is not as a DW but simply as a DSS database for recent data. Since the requirement is to be able to look at 10 years data (eventually) I have no other option.

I think the workaround (read HACK) would be to set the query binding for the partition so that it would immediately generate an error if anyone decided to process it. That and regular backups after each day's processing should keep me sleeping at night.

Is there any tool which support design and modeling for SQL 2005?

ERwin¡¢PowerDesigner¡¢Visio ...?
I want it could created a document according to the DB's schemal.Hi
I would expect most of the larger players will have versions to release when
SQL2005 is launched.
John
"microsoft.public.sqlserver.server" wrote:
> ERwin¡¢PowerDesigner¡¢Visio ...?
> I want it could created a document according to the DB's schemal.
>
>

Is there any tool which support design and modeling for SQL 2005?

ERwinPowerDesignerVisio ...?
I want it could created a document according to the DB's schemal.
Hi
I would expect most of the larger players will have versions to release when
SQL2005 is launched.
John
"microsoft.public.sqlserver.server" wrote:

> ERwin?￠PowerDesigner?￠Visio ...?
> I want it could created a document according to the DB's schemal.
>
>