Database Diagram in MSSMSE

after creating database in MICROSOFT SQL SERVER MANAGEMNET STUDIO EXPRESS, when i right click on diagram node, a menu appears with these 2 options :

1- working with sql server diagrams

2- refresh

how can i make a new diagram unless there is no such thing there?

I do not have the management tools for express loaded but you should be able to do it. When you do go to the database and expand it there is a diagram node. When you first expand this the engine should ask you if you want it to create the objects. You select yes then you are able to right click and create a new diagram. The other option is to use visual stuido 2005 to work with the digrams.

Database Diagram

Is there any benefit as far as performance for creating a database
diagram in SQL Server 2K?
Just wondering...
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0404081120.2820d30@.posting.google.co m...
> Is there any benefit as far as performance for creating a database
> diagram in SQL Server 2K?
> Just wondering...
> lq

No - database diagrams are purely a tool for viewing and manipulating the
schema, they don't have any effect on performance as such, unless you use
them to add/remove constraints or indexes.

Simon

database design:dnamically build where clause

Hi
I am creating a table to store metadata information for certain technical
parameters which are stored in various tables.
These parameters have various criteria to determine if they are compliant to
certain predefined conditions or not.I will explain with an example:
For ex,
paramA is compliant if its value is 50
paramB is compliant if its value is between 0 and 2
paramC is compliant if its value is 35% of another column
paramD is complaint depending on colA,ColB,ColC
If colA = 5 then paramD is compliant if paramD=2.
If colB = 10 then paramD is compliant if paramD=3
If colC =50 then paramD is complaint if paramD=70
paramE is compliant if is 1 or 0
So my table has fields such as
DDId,
ParamName
ParamDBname
ParamTableName
Compliancecondition(varchar 200)
etc.
I need to store the information regarding these compliant conditions so that
I can build my queries like
select <paramName>,case when<compliance condition is true> then 1 else 0,
from <paramDB>.<paramTable>
where<compliancecondition>
but this will result in dynamic SQL.is there a better way to store the
information for these varying compliance conditions?
thanks for your help.
etcLooks like you need something like a rules engine to satisfy your dynamic
needs. Check this nice article on this subject to see if its useful for your
scenario:
http://msdn.microsoft.com/sql/defau.../>
eengine.asp
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"tech77" <tech77@.discussions.microsoft.com> wrote in message
news:7E249C36-147E-4A0A-9B77-42F9F4CA6E6E@.microsoft.com...
> Hi
> I am creating a table to store metadata information for certain technical
> parameters which are stored in various tables.
> These parameters have various criteria to determine if they are compliant
> to
> certain predefined conditions or not.I will explain with an example:
> For ex,
> paramA is compliant if its value is 50
> paramB is compliant if its value is between 0 and 2
> paramC is compliant if its value is 35% of another column
> paramD is complaint depending on colA,ColB,ColC
> If colA = 5 then paramD is compliant if
> paramD=2.
> If colB = 10 then paramD is compliant if
> paramD=3
> If colC =50 then paramD is complaint if
> paramD=70
> paramE is compliant if is 1 or 0
> So my table has fields such as
> DDId,
> ParamName
> ParamDBname
> ParamTableName
> Compliancecondition(varchar 200)
> etc.
> I need to store the information regarding these compliant conditions so
> that
> I can build my queries like
> select <paramName>,case when<compliance condition is true> then 1 else 0,
> from <paramDB>.<paramTable>
> where<compliancecondition>
> but this will result in dynamic SQL.is there a better way to store the
> information for these varying compliance conditions?
> thanks for your help.
>
>
>
> etc

Database Design. Need Advice. Thank You.

Hello,
I am creating a database where:
- I have a Blogs and Folders system.
- Use a common design so I can implement new systems in the future.
Users, Comments, Ratings, View, Tags and Categories are tables common
to all systems, i.e., used by Posts and Files in Blogs and Folders.
- One Tag or Category can be associated to many Posts or Files.
- One Comment, View or Rating should be only associated to one Post or
one File. I am missing this ... (1)
Relations between a File / Folder and Comments / Ratings / View /
Tags / Categories are done using FilesRatings, FoldersViews, etc.
I am using UniqueIdentifier as Primary Keys.
I checked ASP.NET Membership tables, a few articles and few features
in my project, such as renaming files with the GUID of their records.
I didn't decided yet for INT or UNIQUEIDENTIFIER.
I am looking for some feedback on the design of my database.
One thing I think need to improve is mentioned in (1)
But any advices to improve it would be great.
Thank You,
Miguel
My Database Script:
-- Users ...
create table dbo.Users
(
UserID uniqueidentifier not null
constraint PK_User primary key clustered,
[Name] nvarchar(200) not null,
Email nvarchar(200) null,
UpdatedDate datetime not null
)
-- Categories ...
create table dbo.Categories
(
CategoryID uniqueidentifier not null
constraint PK_Category primary key clustered,
[Name] nvarchar(100) not null
)
-- Comments ...
create table dbo.Comments
(
CommentID uniqueidentifier not null
constraint PK_Comment primary key clustered,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
constraint FK_Comments_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Ratings ...
create table dbo.Ratings
(
RatingID uniqueidentifier not null
constraint PK_Rating primary key clustered,
AuthorID uniqueidentifier not null,
Value float not null,
constraint FK_Ratings_Users
foreign key(AuthorID)
references dbo.Users(UserID)
)
-- Tags ...
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(100) not null
)
-- Views ...
create table dbo.Views
(
ViewID uniqueidentifier not null
constraint PK_View primary key clustered,
Ticket [datetime] not null
)
-- Blogs ...
create table dbo.Blogs
(
BlogID uniqueidentifier not null
constraint PK_Blog primary key clustered,
Title nvarchar(400) null,
Description nvarchar(2000) null,
CreatedDate datetime null
)
-- Posts ...
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
BlogID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nchar(1000) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
IsPublished bit not null,
constraint FK_Posts_Blogs
foreign key(BlogID)
references dbo.Blogs(BlogID)
on delete cascade,
constraint FK_Posts_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- PostsCategories ...
create table dbo.PostsCategories
(
PostID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_PostsCategories
primary key clustered (PostID, CategoryID),
constraint FK_PostsCategories_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- PostsComments ...
create table dbo.PostsComments
(
PostID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_PostsComments
primary key clustered (PostID, CommentID),
constraint FK_PostsComments_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- PostsRatings ...
create table dbo.PostsRatings
(
PostID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_PostsRatings
primary key clustered (PostID, RatingID),
constraint FK_PostsRatings_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- PostsTags ...
create table dbo.PostsTags
(
PostID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_PostsTags
primary key clustered (PostID, TagID),
constraint FK_PostsTags_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- PostsViews ...
create table dbo.PostsViews
(
PostID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_PostsViews
primary key clustered (PostID, ViewID),
constraint FK_PostsViews_Posts
foreign key(PostID)
references dbo.Posts(PostID)
on delete cascade,
constraint FK_PostsViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Folders ...
create table dbo.Folders
(
FolderID uniqueidentifier not null
constraint PK_Folder primary key clustered,
[Name] nvarchar(100) null,
Description nvarchar(2000) null,
CreatedDate datetime not null,
URL nvarchar(400) not null
)
-- Files ...
create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
FolderID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nvarchar(400) null,
Description nvarchar(2000) null,
[Name] nvarchar(100) not null,
URL nvarchar(400) not null,
UpdatedDate datetime not null,
IsPublished bit not null,
Type nvarchar(50) null,
constraint FK_Files_Folders
foreign key(FolderID)
references dbo.Folders(FolderID)
on delete cascade,
constraint FK_Files_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade
)
-- FilesCategories ...
create table dbo.FilesCategories
(
FileID uniqueidentifier not null,
CategoryID uniqueidentifier not null,
constraint PK_FilesCategories
primary key clustered (FileID, CategoryID),
constraint FK_FilesCategories_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesCategories_Categories
foreign key(CategoryID)
references dbo.Categories(CategoryID)
)
-- FilesComments ...
create table dbo.FilesComments
(
FileID uniqueidentifier not null,
CommentID uniqueidentifier not null,
constraint PK_FilesComments
primary key clustered (FileID, CommentID),
constraint FK_FilesComments_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesComments_Comments
foreign key(CommentID)
references dbo.Comments(CommentID)
on delete cascade
)
-- FilesRatings ...
create table dbo.FilesRatings
(
FileID uniqueidentifier not null,
RatingID uniqueidentifier not null,
constraint PK_FilesRatings
primary key clustered (FileID, RatingID),
constraint FK_FilesRatings_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesRatings_Ratings
foreign key(RatingID)
references dbo.Ratings(RatingID)
on delete cascade
)
-- FilesTags ...
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
-- FilesViews ...
create table dbo.FilesViews
(
FileID uniqueidentifier not null,
ViewID uniqueidentifier not null,
constraint PK_FilesViews
primary key clustered (FileID, ViewID),
constraint FK_FilesViews_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesViews_Views
foreign key(ViewID)
references dbo.Views(ViewID)
on delete cascade
)
-- Run script
goOn Oct 22, 12:21 am, shapper <mdmo...@.gmail.com> wrote:
> Hello,
> I am creating a database where:
> - I have a Blogs and Folders system.
> - Use a common design so I can implement new systems in the future.
> Users, Comments, Ratings, View, Tags and Categories are tables common
> to all systems, i.e., used by Posts and Files in Blogs and Folders.
> - One Tag or Category can be associated to many Posts or Files.
> - One Comment, View or Rating should be only associated to one Post or
> one File. I am missing this ... (1)
> Relations between a File / Folder and Comments / Ratings / View /
> Tags / Categories are done using FilesRatings, FoldersViews, etc.
> I am using UniqueIdentifier as Primary Keys.
> I checked ASP.NET Membership tables, a few articles and few features
> in my project, such as renaming files with the GUID of their records.
> I didn't decided yet for INT or UNIQUEIDENTIFIER.
> I am looking for some feedback on the design of my database.
> One thing I think need to improve is mentioned in (1)
> But any advices to improve it would be great.
> Thank You,
> Miguel
> My Database Script:
> -- Users ...
> create table dbo.Users
> (
> UserID uniqueidentifier not null
> constraint PK_User primary key clustered,
> [Name] nvarchar(200) not null,
> Email nvarchar(200) null,
> UpdatedDate datetime not null
> )
> -- Categories ...
> create table dbo.Categories
> (
> CategoryID uniqueidentifier not null
> constraint PK_Category primary key clustered,
> [Name] nvarchar(100) not null
> )
> -- Comments ...
> create table dbo.Comments
> (
> CommentID uniqueidentifier not null
> constraint PK_Comment primary key clustered,
> AuthorID uniqueidentifier not null,
> Title nvarchar(400) null,
> Body nvarchar(max) null,
> UpdatedDate datetime not null,
> constraint FK_Comments_Users
> foreign key(AuthorID)
> references dbo.Users(UserID)
> )
> -- Ratings ...
> create table dbo.Ratings
> (
> RatingID uniqueidentifier not null
> constraint PK_Rating primary key clustered,
> AuthorID uniqueidentifier not null,
> Value float not null,
> constraint FK_Ratings_Users
> foreign key(AuthorID)
> references dbo.Users(UserID)
> )
> -- Tags ...
> create table dbo.Tags
> (
> TagID uniqueidentifier not null
> constraint PK_Tag primary key clustered,
> [Name] nvarchar(100) not null
> )
> -- Views ...
> create table dbo.Views
> (
> ViewID uniqueidentifier not null
> constraint PK_View primary key clustered,
> Ticket [datetime] not null
> )
> -- Blogs ...
> create table dbo.Blogs
> (
> BlogID uniqueidentifier not null
> constraint PK_Blog primary key clustered,
> Title nvarchar(400) null,
> Description nvarchar(2000) null,
> CreatedDate datetime null
> )
> -- Posts ...
> create table dbo.Posts
> (
> PostID uniqueidentifier not null
> constraint PK_Post primary key clustered,
> BlogID uniqueidentifier not null,
> AuthorID uniqueidentifier not null,
> Title nchar(1000) null,
> Body nvarchar(max) null,
> UpdatedDate datetime not null,
> IsPublished bit not null,
> constraint FK_Posts_Blogs
> foreign key(BlogID)
> references dbo.Blogs(BlogID)
> on delete cascade,
> constraint FK_Posts_Users
> foreign key(AuthorID)
> references dbo.Users(UserID)
> on delete cascade
> )
> -- PostsCategories ...
> create table dbo.PostsCategories
> (
> PostID uniqueidentifier not null,
> CategoryID uniqueidentifier not null,
> constraint PK_PostsCategories
> primary key clustered (PostID, CategoryID),
> constraint FK_PostsCategories_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsCategories_Categories
> foreign key(CategoryID)
> references dbo.Categories(CategoryID)
> )
> -- PostsComments ...
> create table dbo.PostsComments
> (
> PostID uniqueidentifier not null,
> CommentID uniqueidentifier not null,
> constraint PK_PostsComments
> primary key clustered (PostID, CommentID),
> constraint FK_PostsComments_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsComments_Comments
> foreign key(CommentID)
> references dbo.Comments(CommentID)
> on delete cascade
> )
> -- PostsRatings ...
> create table dbo.PostsRatings
> (
> PostID uniqueidentifier not null,
> RatingID uniqueidentifier not null,
> constraint PK_PostsRatings
> primary key clustered (PostID, RatingID),
> constraint FK_PostsRatings_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsRatings_Ratings
> foreign key(RatingID)
> references dbo.Ratings(RatingID)
> on delete cascade
> )
> -- PostsTags ...
> create table dbo.PostsTags
> (
> PostID uniqueidentifier not null,
> TagID uniqueidentifier not null,
> constraint PK_PostsTags
> primary key clustered (PostID, TagID),
> constraint FK_PostsTags_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsTags_Tags
> foreign key(TagID)
> references dbo.Tags(TagID)
> )
> -- PostsViews ...
> create table dbo.PostsViews
> (
> PostID uniqueidentifier not null,
> ViewID uniqueidentifier not null,
> constraint PK_PostsViews
> primary key clustered (PostID, ViewID),
> constraint FK_PostsViews_Posts
> foreign key(PostID)
> references dbo.Posts(PostID)
> on delete cascade,
> constraint FK_PostsViews_Views
> foreign key(ViewID)
> references dbo.Views(ViewID)
> on delete cascade
> )
> -- Folders ...
> create table dbo.Folders
> (
> FolderID uniqueidentifier not null
> constraint PK_Folder primary key clustered,
> [Name] nvarchar(100) null,
> Description nvarchar(2000) null,
> CreatedDate datetime not null,
> URL nvarchar(400) not null
> )
> -- Files ...
> create table dbo.Files
> (
> FileID uniqueidentifier not null
> constraint PK_File primary key clustered,
> FolderID uniqueidentifier not null,
> AuthorID uniqueidentifier not null,
> Title nvarchar(400) null,
> Description nvarchar(2000) null,
> [Name] nvarchar(100) not null,
> URL nvarchar(400) not null,
> UpdatedDate datetime not null,
> IsPublished bit not null,
> Type nvarchar(50) null,
> constraint FK_Files_Folders
> foreign key(FolderID)
> references dbo.Folders(FolderID)
> on delete cascade,
> constraint FK_Files_Users
> foreign key(AuthorID)
> references dbo.Users(UserID)
> on delete cascade
> )
> -- FilesCategories ...
> create table dbo.FilesCategories
> (
> FileID uniqueidentifier not null,
> CategoryID uniqueidentifier not null,
> constraint PK_FilesCategories
> primary key clustered (FileID, CategoryID),
> constraint FK_FilesCategories_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesCategories_Categories
> foreign key(CategoryID)
> references dbo.Categories(CategoryID)
> )
> -- FilesComments ...
> create table dbo.FilesComments
> (
> FileID uniqueidentifier not null,
> CommentID uniqueidentifier not null,
> constraint PK_FilesComments
> primary key clustered (FileID, CommentID),
> constraint FK_FilesComments_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesComments_Comments
> foreign key(CommentID)
> references dbo.Comments(CommentID)
> on delete cascade
> )
> -- FilesRatings ...
> create table dbo.FilesRatings
> (
> FileID uniqueidentifier not null,
> RatingID uniqueidentifier not null,
> constraint PK_FilesRatings
> primary key clustered (FileID, RatingID),
> constraint FK_FilesRatings_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesRatings_Ratings
> foreign key(RatingID)
> references dbo.Ratings(RatingID)
> on delete cascade
> )
> -- FilesTags ...
> create table dbo.FilesTags
> (
> FileID uniqueidentifier not null,
> TagID uniqueidentifier not null,
> constraint PK_FilesTags
> primary key clustered (FileID, TagID),
> constraint FK_FilesTags_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesTags_Tags
> foreign key(TagID)
> references dbo.Tags(TagID)
> )
> -- FilesViews ...
> create table dbo.FilesViews
> (
> FileID uniqueidentifier not null,
> ViewID uniqueidentifier not null,
> constraint PK_FilesViews
> primary key clustered (FileID, ViewID),
> constraint FK_FilesViews_Files
> foreign key(FileID)
> references dbo.Files(FileID)
> on delete cascade,
> constraint FK_FilesViews_Views
> foreign key(ViewID)
> references dbo.Views(ViewID)
> on delete cascade
> )
> -- Run script
> go
Please, anyone?
Does someone sees a way to improve this?
Thanks,
Miguel

Database Design Theory

I have been tasked with creating a Data Warehouse.

Problem is that old storage vs reporting debate.

I have determined that the data that I will recieve and store will be like follows (simplified form) for expandability

KEY FldKEy FldData DateTime AuditTrail

Daily I will use this data based on use input process this data into the following format and say
if fldkey/ flddata open a cycle.
populate row with null close date
if fldkey/ flddata closes cycle
update row with date

If fldkey/ flddata changes a cutable value
update row

if fldkey/ flddata changes a cutable value (type 2 table)
insert a row into detail update value and obsolete previous row.

KEY DateStart DateEnd FLDDATA1 FLDDATE2 Op_Cl_IND HEADER Record

KEY EFFdate OBSDATE FLDdata3 FLDData4 Detail Records
KEY EFFdate OBSDATE FLDdata3 FLDData4
KEY EFFdate OBSDATE FLDdata3 FLDData4

Problem: FLDKey is a finite count however the max is undefined.

IS there any way to solve the problem of not being able to nail down users to tell you what they want to cut by. What I have been instructed by mgr (old IDMS) is that they wish to see all on the FldData and have the ability to cut by all of it. However the Flddata could be anything (cannot be indexed).

400,000,000 rows at least.

Do I need to nail the users down or am I am missing something.

Sorry if so cryptic

:(Too cryptic, and too much jargon. (Cut?, Cycle?)

But this sure doesn't look like a data warehouse project to me.|||Thought so.

User requirement store a undetermined amount of data about an item

Cannot create a structure with
Key Make Model Year Cost Sold Junked
1 Ford Taurus 97 10000 1/1/98 2/28/06
2 Ford Ranger 97 20000 1/12/99 null

Need to create
Key Fieldname data
1 Make Ford
1 Model Taurus
1 Year 97
1 Cost 10000
1 Sold 1/1/98
1 Junked 2/28/06

As the Number of fields captured is not fixed.

Now I need to sum the that in the data field by a cut of that data.
Total Cost of All 1997 Ford Cars and grouped by the age in months of the auto.

Work of a small table to do this however 400 m rows of data over 18 months Tring to find the data 1997 and Ford in the data field then determining the total dollars of those

Well.

Question is there some thing that I am missing or do I need to get the user to define a more static structure. And transform this into a star schema.

The table structure to store the data (Audit trail)

perhaps datamart is a more appropriate term.|||So you're trying to do the logical vertical table thingee...

Why?

There are sooo many bad reasons for doing this...

The concept is that you want to be able to add new "columns" on the fly

I'll tell you this, if you do go this route, you better make sure to use a partitioned view where the tables are in their own file groups and distributed across many physical disks...

I wonder what you would partition on though|||That is my thought that this a bad idea.

I need to convince my MGR and Users to give me more Defined specs.

Was just asking if there is something that I am missing.

The Goal to the Audit Trail is to capture every poteitial detail of a thing. And to get that info If you have that things ID you can do this. However any and look at every detail about this. the other reason for the data structure of the audit trail is scalability of data storage.

If the users can define the key of a thing and details about that I can use the daily audit file to transpose this information.

I just wanted to make sure that my thoughts were not clouded by preconcieved notions or by lack of knowledge about new techniques.

Thank you|||What you are talking about is known as an EAV (Entity/Attribute/Value) model, and while it is occasionally appropriate, it is very difficult to write code around and I would NEVER recommend it for a "data warehouse".

If you need an unstructured data schema, consider developing in SQL Server 2005 and using the new XML datatype instead of an EAV design.|||Thank You BlindMan for putting a name to my problem. (EAV)
Thank You Brett

I am only using this struture to store an audit trail of a dynamic data structure.

I will persuade the users better to define there business needs so that I may make a true Data Warehouse.

I will use the Audit trail data and evaluate the rows of data that I recieve nighlty to populate the Warehouse. From predifined specs.

I cannot force a change in the data entering the Database.

But once here it is mine all mine.. AH HA HA HA :D|||I think you need to rethink your audit trail. If you are trying to use a single table to store all data changes from any other table, you are going to find the resulting dataset quite unwieldy. Either just store a description of the change that occured, or create separate archive tables for each production to table to store a history of modifications.|||Thank You BlindMan for putting a name to my problem. (EAV)have a look at tony andrews' article OTLT and EAV: the two big design mistakes all beginners make (http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html)

tony's another dbforums stallwart|||To tell the truth...for me, doing audits...simpler is better

Just have an exact copy of the table.

Add 3 columns, HIST_ADD_BY, HIST_ADD_DT, HIST_ADD_TYPE

Create a trigger that inserts into this table for all UPDATE and DELETE Operations and move the entire row

Put no indexes on this table

Boom, audit done

If you want performance on analysis, I'd reccomend that you bcp the data out and load it to a table with indexes (applied after the load)

I would not want to interfere with the triggers at all

MOO|||Thanks all for the reference to the column.

The EAV I am forced to use is a decision of My Management and cannot push them off of that concept. Oh the fate of a peon. I have explained the pros and cons of this decision but this is what has been decided.

The other option that they suggested was to dynamically evaluate the key fields and if a new one appears generate the DDL to add a field to a table of 800+ fields and transpose this data.

They wish to make the data flexible for any forseeable senario without further IT involvment.

If I had total control ....|||They wish to make the data flexible for any forseeable senario without further IT involvment.lol
I've been enjoying the evolution of this thread. I love it when threads like this end with the management concluding that they can conceive of a model that will require no "further IT involvment".

I've never had the misfortune to run into a full blown EAV model (appropriately implemented or not) in the flesh but it's pretty clear with only a little reading and even a substandard imagination like mine that this is precisly where they do not lead you.

Pro - can stick any data in the db
Con - can stick any data in the db
Con - Try getting it out again

"for any forseeable senario" lol.

Ah well - you tried. Best of luck :D|||Put it in writing/e-mail right now that you think XML is the way to go with this. Then save a copy of the document in your CYA file.
Some very complex coding is ahead of you.|||committing to writing that XML is the way to go may backfire on ya...

not that i've got any XML experience, but i've heard horror stories about performance, and since XML has such cachet with management, they may take you up on it and then you could be cooked|||Yes, the performance sucks compared with a standard normalized database, but is probably no worse than an EAV design and with a helluva lot less programming. I would not place data in an XML column unnecessarily, but would reserve it only for data that could not be predefined in the business model.

Database design question

Hi there

I'm in the process of creating an order database that contains tables for products and an order table that contains information for orders. Should the order table have a xref to the products table to show what products are associated or should there be a separate table to show what products are associated for the order (ie a orderProduct table) that would also contain pricing. The reason for my question is for tracking order history. If the price of a product changes and the order table is associated with the product id then old orders will have the new pricing which is incorrect. Would it be better to when an order is place copy the values of price and the product info to an other table?

Thanks

Good thinking. Separate table. OrderProducts. OrderID FK, ProductID FK, OrderPrice. OrderPrice is the price at the time of the order.

Incidentally, can anyone delete a product if your customer discontinues it? If so, you might want to think about a YesNo/bit column to flag a discontinued product for suppression rather than wiping it from the database.

|||

I would recommend that you do that, for exactly the reason you state. I have worked with MS Commerce Server 2000 and it had a similar database design. Here is a simplified example from my vague memory:

Order
OrderID (primary key)
OrderNumber (human readable order number)
CustomerID (foreign key to customer)
Date
SubTotal
Tax
Shipping
GrandTotal

Product
ProductID (primary key)
Active
SKU
Name
Description
Price

OrderItem
SKU
Name
Price
Quantity
Total

The idea is that any field that could change throughout the lifetime of a product should be stored with the order in order to record exactly what they ordered. You may even think about moving the Price for Products out into a ProductPrice table like the following.

Product
ProductID
SKU
Name
Description

ProductPrice
ProductID (foreign key to product)
EffectiveDate (date this price becomes effective)
Price

This design allows you to change prices ahead of time and specify when they become effective. To get the current price you would select the ProductPrice with the highest EffectiveDate that is greater than or equal to today's date. This may be totally unnecessary though, it's just an idea.

Mark

|||thanks for the replies guys that does help clarify things. I'm currently having the same type of issues with a room booking database design. I want to have tenants book certain rooms for specified time intervals. The tenants can book rooms (rooms are stored in a table) and select things like room setup (configurations are stored in a table). All the tables have an active flag on then. I also have a bookings table but unsure as to how to store the rooms and configurations that are selected. Should i be using ids in the booking table? What happens if the rooms become inactive down the road and the client was to view past bookings? Do i leave out the check on the active flags and show the rooms anyway? I believe that i need the room ids in order to validate new bookings against what is already booked. If the admins want to edit bookings and say a room or configuration that was previously active is now inactive would i force the admin to select a new configuration? tables such as room and room configuration do not have an interface to change values or inactivate them, i'm just wondering for the future if they say they want to add a room and remove a room then past bookings will get affected.

Database Design of Categories

I am creating a website that has categories such as:

Sports -> Soccer -> Soccer Shoes

I can not think of an effectively way to implement this into a database. Please help.

Here is a website that has a bunch of sample database designs. If you don't find exactly what you want, the process of looking thru the other designs should help you.

http://www.databaseanswers.org/data_models/

A good data modeling book is by David Hay, Data Modeling Patterns: Conventions of Thought. He gives sample high-level data designs and talks thru different modeling alternatives so you fully understand the implications of subtle modeling differences.

|||

If you post what you've tried and why you didn't like it, I'll be happy to give you ideas on how to improve it.

|||

Thanks, that is a really cool site! I haven't looked at all the database designs because there are a lot of them but one that I found is #39 Books and Libraries. They have a "categories" table but all it stores is the CategoryID and the CategoryName. With just that information how to do you know where the category is in the hierarchy of categories? For example, you have Literature -> Books -> Fiction, say Fiction has CategoryID 20, how do you know where Fiction is in the hierarchy just by knowing the CategoryID?

I came across aneBay developer site that explains how they store their categories. What they do is they store the CategoryID but also the ID of it's parent. This way you know where it exists in the hierarchy. This is what the table would look like:

Category
CategoryID
ParentID
CategoryName
IsLeafCategory

What does everyone think of this solution? Does anyone have any alternative solutions?

|||

A better column name for ParentID would be ParentCategoryID. It's always better to be very clear.

This is a more powerful model than Literature->Books->Fiction because it can handle a "infinite" number of hierarchical levels.

It is also a more complicated one to use than a model in which you force the same number of levels in the hierarchy by using a different table for each level in the hierarchy.

IsLeafCategory is a redundant piece of information in that it can be deduced when required. I would not make use of it unless you have a specific need for it (because then you have to write code to make sure it is always correct).

Database design

I am creating database tables for company testimonials. Database columns: name, position, companyname, comment, service we provided.

My question is that for each company - may have a multitude of different services from us, and different people with different positions in the same company may make comments.

What is best practice for putting this db structure together?

Thanks

Andrew

You have 4 principal objects: Company, Person, Service and Testimonial. That leads to 4 tables:

Company
CompanyIDPK
Address1
Address2
etc...

Person
PersonIDPK
Title
Initials
FirstName
Surname
Position
CompanyIDFK
etc...

Services
ServiceID
Service

Testimonials
TestimonialIDPK
Testimonial
PersonIDFK
ServiceIDFK

So now each testimonial is linked to a person and a service, and each person is linked to a company, so a testimonial is linked to a company through the person. You can add another table:

CompanyServices
CompanyIDFK
ServiceIDFK

to manage the relationship between companies and all the services they avail themselves of, regardless of whether they comment on it.

Database Design

I am creating a knowledge base. I want the user to be able to choose a category such as Hardware, Software, Etc. then I want them to be able to choose what type of software such as word, excel, ets.

It will then display in a gridview problems, solutions, submitted by, last updated, and review date.

The gridview will allow users to update solutions and I would also like the ability for technicians to be able to add new Problems/solutions.

What would be the best way to handle this?

Any tips are appreciated.

Rick

As far as the database design goes, I would make it relational using the structure similar to the one below.

Table: Categories
Columns: CatID, Category

Table: CatTypes
Columns: CatTypeID, CatID, CatType

Table: KB_FAQ
Columns: KB_ID, Problem, Solution, UserID, LastUpdated, LastReviewed

Table: KB_CatTypes
Columns: KB_ID, CatTypeID

Table: Users
Columns: UserID, UserName, FirstName, LastName, Password

|||In Visual Studion Web Developer express, where do I setup relationships?|||I figured it out. Thanks for all your help.