|
I've only been in database design a few years, and have learned a lot of things the hard way. But I'm sure there a lot of other things I haven't learned yet, or am *about to* learn them the hard way.
The link you provided, Rocky# wrote: progress-database-design-guide/[^]
says in one place:
"primary keys with business meaning as well as composite keys are a bad idea."
This I did not know, and even now am not convinced of. I have tended to make my designs exactly the opposite. Let me give an example of a table I recently designed.
CREATE TABLE tblScheduledInventory(
asofDate SMALLINT -- number of days since Jan 1, 2000
, partsGroupID SMALLINT -- REFERENCES tblPartsGroups
, locationID SMALLINT -- REFERENCES tblLocations
, statusCode SMALLINT -- "installed", "spare", etc.
, inventoryTypeID SMALLINT -- "active", "in-reserve", etc.
, CONSTRAINT pkScheduledInventory PRIMARY KEY
(asofDate,partsGroupID,locationID,statusCode,inventoryTypeID)
, cnt INT
);
I need the five fields within the PK to be compositely unique. In addition to that, I KNOW that during use of this table, the records are going to be "marched-thru" in a date-order, and for each date, the records will be traversed in a parts-grouping order. Therefore, I WANT the clustered index (the actual ordering of records within storage) to be by asofDate, then by partsGroupID. This should produce faster queries since all the records of interest will be clustered near one another, and not scattered all over the hard-drive. The only way to achieve this (that I know of) is to make the primary key a composite index of the items that need to be unique, with the most important item being first in the list.
Perhaps someone could shed some light on *why* this might be a bad design practice. Examples would really help me understand.
BTW, in other readings I've discovered there are alternate opinions regarding this point. Some say the way I do it is OK.
David
---------
Empirical studies indicate that 20% of the people drink 80% of the beer. With C++ developers, the rule is that 80% of the developers understand at most 20% of the language. It is not the same 20% for different people, so don't count on them to understand each other's code.
http://yosefk.com/c++fqa/picture.html#fqa-6.6
---------
|
|
|
|
|
thanks for ur response David its really nice!
and uknow I believe in one thing: "Rules always have exceptions"... Generally they do say n its true to quite some extent that one should avoid making unnecessary composite PKs when a single attribute PK shud do. Well actually the thing is that having unnecessary composite PKs can make ur design vulnerable to having some anomalies u know it can fail satisfy 2nf. But still it all depends on ur particular scenario bcz in a way ur scenario can be peculiar to many.
I think ur idea there is quite intelligent and looks quite good to me mate!
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
Thank you for your help 
I've been quite new into this - and since I've lost my mentor, any help is more than welcome.
|
|
|
|
|
ur welcome mate!
someday every buddy has to become a mentor himself.
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
sorry if in the wrong Forum, but I have a question about creating an Access form that works off of 2 tables.
Here is what I have:
tbl_Inventory: RecID, Name, Date, PartNo, Description<br />
tbl_Defects: RecID*, DefectType (RecID* matches RecID in tbl_Inventory)<br />
tbl_DefectTypes: ID, Descriptive_Value<br />
I then have a form where a user can manually enter the values to fill tbl_Inventory. ONE of those fields asks for Defect Type (a combo pulldown control). This form works with tbl_Inventory and tbl_Defects. When there is a defect, tbl_Defects gets the RecID from tbl_Inventory and assigns Defect Type (mechanical, operator, obsolete)
How do I establish my form's (Record) Source? Do I use both tbl_Inventory and tbl_Defects? tbl_Inventory actually has 23 fields and when I tried to use the SQL builder for Record Source of the form, I ran out of "space" (columns) for all my fields.
Any where I can look to get a quick tutorial on using more than one table as the source for ONE form and have the two tables synched when I update in the form?
Much thanks to this newbie Access developer. 
Johnny
|
|
|
|
|
You can use use a query as the source for one form.
|
|
|
|
|
Thanks! Will try that. There was something amiss the first time I tried using the SQL builder (?) as noted in my comment about more fields than columns. Will roll my own SQL statement and should be good.
Thanks again.
Johnny
|
|
|
|
|
I'm using VS 2005 and have installed SQL Server Express Edition also. The documnentation says that I can add a database to an open project by going to the Project / Add New Item menu command and selecting the Database object from the list of templates. That would be nice if it was there. My list has things like Stored Proc, Triggers, Datasets, etc. All sorts of things that would be part of a database, but not a database itself. How do I add/connect to a local SQL Server Express database? Thanks.
|
|
|
|
|
is there a way to create an ado query with multiple data sources? i want tojoin three queries and take those results and insertt it into a fourth? thanks.
|
|
|
|
|
No. However, if you are using SQL Server you can set up linked servers to other data sources e.g. Oracle, Access, Excel and write queries that link objects in the linked servers to native SQL Server objects.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I am using SSRS 2005. Trying to connect reportserver remotely. and trying to fetch reports and collect on a treeview. But getting following error. This works fine, when the web server & sql server are on the same system. I mean works fine locally. Please help me.
Error::
System.Web.Services.Protocols.SoapException: The item '/Sajan Reports' cannot be found. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ItemNotFoundException: The item '/Sajan Reports' cannot be found.
at Microsoft.ReportingServices.Library.ListChildrenAction.PerformActionNow()
at Microsoft.ReportingServices.Library.RSSoapAction.Execute()
at Microsoft.ReportingServices.WebServer.ReportingService2005.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.WebServer.ReportingService2005.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems)
Source codes
Global.asax.cs
**************
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Services.Protocols;
using Microsoft.ReportingServices.Library.Soap2005;
///
/// Summary description for Global
///
public class Global : System.Web.HttpApplication
{
public void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
}
public void Application_End(object sender, EventArgs e)
{
// Code that runs on application startup
}
public void Session_Start(object sender, EventArgs e)
{
// Code that runs on application startup
Session["Uid"] = "";
}
public void Session_End(object sender, EventArgs e)
{
// Code that runs when a session ends.
}
// Report Server defines the URL to root of the
// Reporting Services home page.
public static string ReportServer
{
get { return "http://10.10.40.2/reportserver"; }
}
// ReportPath, when appended to the ReportServer property,
// will define the root of the report search.
// For example, to view all available reports on the report
// server, use "/" as the ReportPath. Setting the value to
// "/MyDemoReports", would only show reports and subdirectories
// under http://ReportServer/MyDemoReports.
public static string ReportPath
{
get { return "/Sajan Reports"; }
}
// There are a couple places where we need to do string
// manipulation to add and remove path seperators -
// sometimes the seperator needs to be passed as an array
// of char, othertimes as a string. These two properties
// simply define both so the code is a little cleaner
// when we do the string munging.
public static char[] PathSeparatorArray
{
get { return pathSeparatorArray; }
}
public static string PathSeparatorString
{
get { return pathSeparatorString; }
}
protected static char pathSeparator = '/';
protected static char[] pathSeparatorArray = { pathSeparator };
protected static string pathSeparatorString = new string(pathSeparator, 1);
}
RenderReport.aspx.cs
*********************
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class RenderReport : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
ReportViewer1.ServerReport.ReportServerUrl = new Uri ("http://10.10.40.2/reportserver");
ReportViewer1.ServerReport.ReportPath = Global.ReportPath + Request["Path"];
}
}
}
Fetching and collecting on treeview with the following code
***********************************************************
rService.Credentials = System.Net.CredentialCache.DefaultCredentials;
catalogItems = rService.ListChildren(Global.ReportServer, true);
TreeView_Reports.Nodes.Clear();
RadTreeNode node16 = new RadTreeNode();
node16.Text = "Report";
TreeView_Reports.Nodes.Add(node16);
TreeView_Reports1(TreeView_Reports, node16, catalogItems);
|
|
|
|
|
From the error message, it looks like the ServerReport.ReportPath property has been set with the report name missing. Is the value of Request["Path"] correct?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
ServerReport.ReportPath is given in RenderReport.aspx page. Error is comming with ReportPath in the second line of the following code which is the next step:
Is there any settings with using namespaces, missing .dlls or missing web reference. Please help me.
Fetching and collecting on treeview with the following code
*****************************************************************
rService.Credentials = new NetworkCredential("administrator", "password@sajan");
catalogItems = rService.ListChildren(Global.ReportPath,true);
TreeView_Reports.Nodes.Clear();
RadTreeNode node16 = new RadTreeNode();
node16.Text = "Report";
TreeView_Reports.Nodes.Add(node16);
TreeView_Reports1(TreeView_Reports, node16, catalogItems);
After changing report path in Global.asax from get { return "/Sajan Reports"; } to get { return "/"; }
I am getting following error. Can you please help me.
Error:
System.Web.Services.Protocols.SoapException: The permissions granted to user 'MANOJ-SAJAN\IUSR_MANOJ-SAJAN' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'MANOJ-SAJAN\IUSR_MANOJ-SAJAN' are insufficient for performing this operation.
at Microsoft.ReportingServices.Library.ListChildrenAction.PerformActionNow()
at Microsoft.ReportingServices.Library.RSSoapAction.Execute()
at Microsoft.ReportingServices.WebServer.ReportingService2005.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems)
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.WebServer.ReportingService2005.ListChildren(String Item, Boolean Recursive, CatalogItem[]& CatalogItems)
-- modified at 5:47 Friday 16th November, 2007
|
|
|
|
|
The following code is not working. This will make the tree. The following code line is not working. This should retrieve no. of reports from report server and put in array, catalogItems. Based on the number of reports the another loop will retrieve names of the reports and will populate the tree view.
catalogItems = rService.ListChildren(Global.ReportPath,true);
Help me please.
-- modified at 7:33 Monday 19th November, 2007
|
|
|
|
|
Hi,
I have a price, and I am a bit confused as to what the data type must be, what's best? I'm running SQL Server 2005.
Thanks
Brendan
|
|
|
|
|
|
hai all,
i had write one storedProcedure,i that i got two fields(columns).
i just want to know about how to find the %(percentage) of the two columnn(fields).
Thanks in advance
Subbu
Thanks
Subbu.
|
|
|
|
|
If you want to get the percentage of column1 against column2, and you have them stored as integers, then you need to force a cast on them so that they can be divided.
select cast(col1 as decimal(10,2)) / cast(col2 as decimal(10,2)) * 100
from table_1
|
|
|
|
|
Hi,
i'm a beginner in asp.net programming and web for that matter,
and in need of some help.
i use an unknown amout of tables that i need to view, lets assume the connection string and select query are entered by the user.
i need in the page_load or somewhere to create a dataset and set the gridview to it. the grid view is a static control on the web form.
now i am using:
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("con str");
SqlDataAdapter ad = new SqlDataAdapter("select str", con);
DataSet ds = new DataSet();
ad.Fill(ds, "test");
con.Close();
this.Controls.Add(data);
theGrid.DataSourceID = data.ID;
theGrid.DataSource = ds;
theGrid.DataBind();
}
this works, but each click/change that causes a postback will call page_loaf again. i tried to define the ds variable as private var of the class, and then put the 3 first lines in an if (!postback) {...} statment.
didn't work, the vars are recreated on each post back.
what is the solution for this?
how can i create a grid that will be saved without re binding it?
(and if i don't add the ds to the form as a control, it just doesn't bind)
help!!!
thanks, koren.
kosh2059
|
|
|
|
|
|
I am working with Excel sheets via ODBC, reading and writing to.
I do have a source Excel which I can read perfectly, with a column with long text, some of them, longer than 255. As said, I can read them.
But when writing to destination, I can only write the first 255 positions.
I tried all of this:
- using both ODBC versions for Excel (4 and 12)
- with all possible char types (char, varchar, longvarchar, nvarchar, ....)
When I specify column lenght as longer than 255, this happens:
[DataStage][SQL Client][ODBC][Microsoft][Controlador ODBC Excel]Preccision value not valid
and when setting precision to 255, obviously:
[DataStage][SQL Client]Data has been truncated
What it is really strange to me, is that I can read the column with no problem (I must say, only with Excel ODBC Driver version 4, with version 12 data was truncated on source). I even specify the column as varchar - 500, and no warning was raised!!!
Anybody can help? Thanks very much
|
|
|
|
|
This article[^] may provide an explanation for your problem.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi,
i have written a code through which i am storing a image in the access databas e its getting stored in the long binary int format My aim is to copy that one to another row .....
code for storing is
byte[] b = (byte[])datareader[0];
MemoryStream st = new MemoryStream();
st.Write(b, 0, b.Length);
picturebox.backgroundimage = Image.FromStream(st);
if any one knows how to do this plz let me know
sindhu tiwari
its me sid
|
|
|
|
|
Did you try searching the site before you posted? It took me 5 seconds to find this[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hello,
I've used the BindingSource Control and have two tables in my database.
On a single Windows form, i've 'dragged n dopped' coloumns from both the tables.These two tables are related by master-child relationship.
My Problem is as follows:
When i click on the '+' button to add a record and then click on 'save'.
Data is successfully inserted into the parent table but not in the child table.
I've checked the designer code and it DOES have a function to insert into the child table. Yet the insertion fails.(There are no exceptions raised either!!)
Any help is appreciated...
Thanking all in advance.
Siddy!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
