Table Relations, I Need An Advice Please
Jan 9, 2008
hello,
i started making my database some time ago, and therewith i maked some relations between my tables
at the beginning, at Tables, at the UserId row, i had uniqueidentifier. and because i had some difficulties when trying to get the username who corresponds with that uniqueidentifiers i've discarded that and replacing the uniqueidentifier with varchar(255) where i am writing dirrectcly User.Identity.Name.
Because of that, i had to delete some of the tables relations, because i can make relations only by unique variables (witch in fact are unique but database can't know that)
What i should do now? remake the tables with UserId as uniqueidentifer or i should let it so without no relations between them? (witch i don't think is good)
thanks
View 6 Replies
ADVERTISEMENT
Mar 26, 2008
I'm looking for some input about how I should setup my database tables. Here's the setup - There are several Report Forms that users can fill out and each one has its own table (I'll use Report1 and Report2 for examples). There are several other tables that relate to these Report tables, for example, a table named Pictures. If a user is filling out Report1 or 2, they could add a picture to it. The picture information is stored in the Picture table, along with the ReportID of the report it belongs to.
Report1 (table)intReportID (pk)vcFirstNameetc...Report2 (table)intReportID (pk)vcFirstNameetc...Pictures (table)intPictureID (pk)intReportID (fk - to match ReportID in appropriate Report table - not being allowed...)vcFilePathetc...
Right now, I've two relationships setup for the pictures table - one with the picture table ReportID as a FK to the Report1 table's ReportID PK, and another setup the same way, but for Report2.It's not letting me do this because it's looking for a matching ReportID in both the Report1 and Report2 tables for each entry in the Pictures table. A match will only ever exist for one or the other, never both.
In addition to Pictures, I have about 9 other tables that work the same way. I also have about 7 Report tables total. I'd really like to set something up so if I delete a record from a report table, it will delete all matching entries from the other tables. Does anyone know how I can go about setting this up?
Thanks very much!
View 5 Replies
View Related
Jun 13, 2005
Code:
TBL1
ID User
1 Tom
2 Alice
3 Alex
4 Melissa
5 Carl
Code:
TBL2
ID User WebSite
1 Tom http://www.Tom.com
2 Alice http://www.Alice.com
3 Alex http://www.Alex.com
4 Carl http://www.Carl.com
I want to join tables above in only one query.
I tried something I couldn't.
there are 5 users in TBL1
TBL2 is for their web sites records.
But while tom,alice,alex and carl have web site,Melissa hasn't.
I want to print like below
Code:
Tom http://www.Tom.com
Alice http://www.Alice.com
Alex http://www.Alex.com
Melissa
Carl http://www.Carl.com
my tests, although users who have web site were printed, Melissa wasn't printed.
Although she hasnot a website, I want to get her , too.
View 9 Replies
View Related
May 27, 2004
Hi,
we have a problem with "one-to-many relations between fact table and dimension table". Take the example of table "LOGGEDFLAW" which is related one-to-many to the table "LOGGEDREASON. "LOGGEDFLAW" includes the column "FLAWKEY" and "LOGGEDREASON" includes the column "REASONKEY" and essentiallay the column "FLAWKEY" as foreign key. Now assume that we have the following records in there:
LOGGEDFLAW
1) FLAW1
2) FLAW2
LOGGEDREASON
1) REASON1,FLAW1
2) REASON2,FLAW1
3) REASON3,FLAW2
Now assume, that "LOGGEDFLAW" is the facttable and "FLAWCOUNT" is the measure with the source column "FLAWKEY" in which we want to count the number of FLAWs. As you see in the example the number of FLAWs is 1 for "FLAW1" and "FLAW2". Microsoft Analysis Server generates the value of 2 for the number of FLAWs "FLAW1" because of the one-to-many relationship to the table "LOGGEDREASON". In the attached ZIP File you find :
- a MDB File with the described example
- a screenshot from the cube constructed in AS
- a screenshot from the result table generated with AS.
The question: How is it possible to calculate the measure "FLAWCOUNT" correctly, ignoring the records generated by the one-to-many relationship?
Best regards,
Thorsten
View 5 Replies
View Related
Dec 16, 2006
Hi,How to get list of all relations of certein database's table?
View 7 Replies
View Related
Feb 28, 2007
I have a table tblFriends:UserCodeOwner FriendCode5 545 1235 4785 49054 5123 5478 5478 500490 5490 500500 478500 490500 600600 500As you can see I store each relation twice, i've done that because now I can create a clustered index on UserCodeOwner for faster searching.What I want is the following:I want to count how many steps it takes to get from one user to another via their relations and also via which usercode it goes.so: from 5 to 123 is one step.5-123from 5 to 500 is 2 steps via either 478 or 490I want to get a result like this:5-478-500and5-490-500from 5 to 600 is 3 steps:5-478-500-6005-490-500-600Does anyone have a good start for me on how im able to return such results and how I can search through the table most effecively?
View 3 Replies
View Related
Mar 18, 2004
.
Hi,
How can I make relations between two tables using query? Tables are already there with data.
Regards
View 3 Replies
View Related
Mar 17, 2008
ok - I have to allow my user to enter in from date - to date (multiple date selections) and these dates will be marked as holiday..
user will also be able to choose a date selection (like march 2-march 5)that was added and remove it from being marked as holiday or add new date selections.
now I need to be able to based on a date lookup whether it is a holiday or not.
what's the best way to set up the table?
should I set it up with fromdates = todates or a table with each individual date and then mark it.
what do you suggest as far as the table set up?
View 1 Replies
View Related
Mar 1, 2006
I am creating a table that will store my companys annual punctualityscale. What is the best way to name the columns? Here is some samplerows with data.--------------------------------------DPT_CODE_1 | 300 | 121 | 120 | 61 | 60DPT_CODE_2 | 360 | 241 | 240 | 121 | 120Thanks for any advice.Aplatfl
View 2 Replies
View Related
Jan 27, 2007
Hi,
I have the following two tables in MS SQL 2000
1.Products:
ProdID intProName char(10)
2.Orders:
OrderID intProdID intOrderDate DateTimeQuantity int
I want to join these two tables to form the following result format:
_________Prod1__Prod2__Prod3__Prod421/01/07__1______0_______1_____0___22/01/07__5______1_______2_____3___23/01/07__8______0_______1_____2___24/01/07__3______3_______4_____3___25/01/07__2______0_______1_____4___26/01/07__1______2_______6_____2___
So the first row would have the product name, Left hand column has the order date and then the sum of all the orders within
Any pointers on how to achive this would be great.
View 2 Replies
View Related
Jan 31, 2005
I have asked a similar question on SQLTemp.com, but I thought I would ask here in case there are people here that don't visit SQLTeam and can help me.
I have to tables:
tbSiteworkPriceList
ItemID | Descr | Material | Labour | Travel | Boarding
-----------------------------------------------------
1 | Finishing | 0.25 | 28 | 42 | 65
tbSiteworkCostTypes
CostTypeID | Descr
-------------------
1 | Materials
2 | Labour
3 | Travel
4 | Boarding
Now, the tabels above are a sitework price list for an estimating package. The tbSitworkCostTypes table is used to trace the value of the particular item back to an account after the item is added to a takeoff. My questions are:
1) How should I tie each items variable (Material, Labour, Travel, Boarding) to its appropriate cost type?
2) Should I divide the table tbSiteworkPriceList above into 2 tables as shown below?
tbSitworkPriceList
ItemID | Descr
1 | Finishing
tbSiteworkCostItems
fkItemID | fkCostTypeID | Price
-------------------------------------------
1 | 1 | 0.25
1 | 2 | 28
1 | 3 | 42
1 | 4 | 65
This seems to be a little better in the point of view that each item variable is linked to the appropriate cost item by joining the tables tbSiteworkCostItems to tbSiteworkCostTypes. They have to be linked because after the takeoff is generated, the dollar values have to be imported into an accounting system where the account code and cost type code are determined by the "CostTypeID". Any thoughts?
Mike B
View 1 Replies
View Related
Apr 9, 2008
I've been assigned to do the data access layer for an existing SQL database created by someone else. Amazingly enough, the primary table, the one containing all of the records that are central to the db and the whole project, has over 3000 entries and no unique identifer (autonumbered ID) column. The data in all of the other columns is repetitious so none of them can be used as a primary key. I have added a column called TaskAssignmentID, designated as an int data type. The goal is to somehow autofill this column with sequential numbers and then designate that column as the primary key. (don't you just love fixing other people's mistakes?) Any suggestions/examples on how to accomplish this the easiest, most efficient way?
For reference purposes, the table is called tbl_MCHS and the newly created column is called TaskAssignmentID. The data type of the column can be changed, if that would make it easier.
Tools available include Visual Studio 2005 (very familiar) and SQL Server 2005's SQL Server Management Studio (less familiar).
Things I am restricted from doing include blowing away the table and starting over or strangling the previous developer (I asked about both). :-)
Any and all suggestions, steps, or examples will be appreciated.
Thanks,
John
View 1 Replies
View Related
Jul 23, 2005
Hi,I have two tables Table A and B, below with some dummy data...Table A (contains specific unique settings that can be requested)Id, SettingName1, weight2, lengthTable B (contains the setting values, here 3 values relate to weightand 1 to length)Id, Brand, SettingValue1, A, 1001, B, 2001, null, 3002, null, 5.3(There is also a list of Brands available in another table). No primarykeys / referential integrity has been setup yet.Basically depending upon the Brand requested a different setting valuewill be present. If a particular brand is not present (signified by anull in the Brand column in table B), then a default value will beused.Therefore if I request the weight and pass through a Brand of A, I willget 100If I request the weight but do not pass through a brand (i.e. null) Iwill get 300.My question is, what kind of integrity can I apply to avoid the userspecifying duplicate Ids and Brands in table B. I cannot apply acomposite key on these two fields as a null is present. Table B willprobably contain about 50 rows and probably 10 of them will be brandspecific. The reason its done like this is in the calling client code Iwant to call some function e.g.getsetting(weight) .... result = 300Or if it is brand specificgetsetting(weight,A) ..... result = 100Any advice on integrity or table restructuring would be greatlyappreciated. Its sql 2000 sp3.Thanksbrad
View 9 Replies
View Related
Feb 12, 2008
All,
I have a situation where I need to identify redundant rows within a table. Here is the schema of the table:
create table Temp.Response (
TempKey int identity(1,1) not null primary key clustered,
ResponseId char(27) not null,
StudentUin char(9) not null,
TemplateId char(27) not null,
MidEndFlag char(3) not null
)
Here is a sample dataset that represents the production data:
TempKey | ResponseId | StudentUin | TemplateId | MidEndFlag
1 2008-02-12-08-10-43-3434648 317003316 2008-01-31-10-12-27-4882454 Mid
2 2008-02-12-08-11-40-5279829 317003316 2008-01-31-10-12-27-4882454 Mid
3 2008-02-11-21-29-12-1254611 516007344 2008-01-31-10-32-26-2359751 Mid
4 2008-02-11-21-30-34-7326988 516007344 2008-01-31-10-32-26-2359751 Mid
5 2008-02-11-21-31-24-2804312 516007344 2008-01-31-10-32-26-2359751 Mid
6 2008-02-11-21-31-47-1742947 516007344 2008-01-31-10-32-26-2359751 Mid
7 2008-02-11-18-52-25-3689636 614001463 2008-01-31-10-32-26-2359751 Mid
8 2008-02-11-18-54-11-7500029 614001463 2008-01-31-10-32-26-2359751 Mid
9 2008-02-11-22-13-59-9139208 614001606 2008-01-31-10-32-26-2359751 Mid
10 2008-02-11-22-14-50-5822454 614001606 2008-01-31-10-32-26-2359751 Mid
11 2008-02-11-22-15-47-6257351 614001606 2008-01-31-10-32-26-2359751 Mid
12 2008-02-11-23-23-31-4431851 614001756 2008-01-31-10-32-26-2359751 Mid
13 2008-02-11-23-24-06-4806990 614001756 2008-01-31-10-32-26-2359751 Mid
I need to identify the ResponseId values for rows that contain redundant StudentUin/TemplateId/MidEndFlag values, so that I can delete those rows. ResponseId, while not the primary key, is a unique value in this dataset. I thought I might use a cursor to parse this, but the real dataset is exceedingly large, and would like a set-based solution.
Best,
B.
View 3 Replies
View Related
Aug 2, 2004
Hi,
I'm about to embark on writing some code in perl or VBscript that automatically synchronises a constantly updated Access database with an MSSQL database.
I know MSSQL has an import tool built into Enterprise manager but I'm wondering if theres a stored procedure that does this?
The way I'm thinking of doing it is to read the all the access tables into separate hash arrays and then INSERTing them into the MSSQL database after checking for any duplicates. This all sounds a bit time consuming (there are a large number of tables) and processor intensive.
If anyones done anything like this before, I'd love to hear their views......!
Thanks!
View 9 Replies
View Related
Feb 22, 2008
Is there a fast way to see, which relations a table has for his PK?
View 2 Replies
View Related
Aug 21, 2004
I am using MSDE Manager for my database, and I was wondering, if data is entered on one table, IE UserID, shouldnt that same userID pop up in the related column on the foreign table as well?
View 1 Replies
View Related
Jun 14, 2007
Maybe a stupid question but...
is it posible to add relations in sql compact databases (.sdf) ?
I tried in server explorer (in orcas) and there is no option for relations. Also .sdf file cannot be opened in SQL Server Express Management Studio SP2 because is version 3.5.
View 1 Replies
View Related
Aug 28, 2007
Hi There,Im trying to display a list of results and have extra information pulled from another table. Please read on as this isnt what you think, just hard to explain but I will try.In Table 1 I have a set of Orders, example data below.Order 1, Line 1Order 1, Line 2Order 1, Line 3Now I have another table to record what lines a user would like to monitor, for this example the user will monitor these lines.Line 1Line 2but not Line 3I need to display a single grid view using the least amount of queries possible to diplay the following resultOrder 1, Line 1, MonitoredOrder 1, Line 2, MonitoredOrder 1, Line 3, NOT MonitoredI have tried viewed, but with the relationships it only displayes what is avialable in both tables, I have also thought about using a DataSet relationship with two queries through the code, but am really wondering if anybody knows of a simple and low overhead way of producing this result? Mayby stored procedures?Kind Regards,Gareth
View 2 Replies
View Related
Aug 9, 2000
Hayyy
I have just made a database with 3 tables, they are now connectet with relationships.
My problem is when I write my select statement:
select * from table1 a, table2 b where a.ID=2.
I will get all the ´records from both the tables, I just want One record. Then I addes some too my select statement so it now looks like this:
select * from table1 a, table b where a.ID=2 AND a.No=b.No
Now I got the right result, but if this is the only way to do it, there is no recent to make all the relations i the DB between the tables.
Thank You... Jonas
View 1 Replies
View Related
May 4, 2004
Hi,
I build a local cube from a relation database. In the database there are 1:n relations.
Is there a way to handle 1:n relations?
For example:
I have a table LOGGEDFLAW and a table LOGGEDREASON with a 1:n relation between them. We create a select statement of these tables and as an result we get duplicate records of LOGGEDFLAW each time more than 1 record of LOGGEDREASON are associated to 1 record of LOGGEDFLAW - this is the standard result I get with an relational JOIN operation. Now I want to count the LOGGEDFLAWs without the duplicates generated by the 1:n relationship.
Best regards,
Thorsten
View 2 Replies
View Related
May 23, 2006
Dim verilaDataAdapter1 As SqlCeDataAdapter
Dim verilaDataAdapter2 As SqlCeDataAdapter
Dim iliski As DataRelation
Dim Sorgu1 As String
Dim Sorgu2 As String
Dim A1Dataset As DataSet
Dim parametre1 As SqlCeParameter
Dim parametre2 As SqlCeParameter
Dim ssceconn As New SqlCeConnection("Data Source =Program FilesAtisoftDAatisoft.sdf")
ssceconn.Open()
Dim sqlInsertRow As SqlCeCommand = ssceconn.CreateCommand()
Sorgu1 = "SELECT * FROM StokKartTbl WHERE StokGrupKodu like @StokGrupKodu Order By Stokismi ASC;"
Sorgu2 = "SELECT * FROM SarzKartTbl WHERE SarzID like @Sarzid"
A1Dataset = New DataSet
verilaDataAdapter1 = New SqlCeDataAdapter(Sorgu1, ssceconn) 'StokkartTbl
parametre1 = New SqlCeParameter("@StokGrupKodu", SqlDbType.NText, 25)
parametre1.Value = Trim(Mid(CBStGrupAciklama.Text, 1, 6)) + "%"
verilaDataAdapter1.SelectCommand.Parameters.Add(parametre1)
verilaDataAdapter1.Fill(A1Dataset, "StokKartTbl")
verilaDataAdapter2 = New SqlCeDataAdapter(Sorgu2, ssceconn) 'SarzKartTbl
parametre2 = New SqlCeParameter("@Sarzid", SqlDbType.NText, 50)
parametre2.Value = Trim(Mid(CBSarzID.Text, 1, 25)) + "%"
verilaDataAdapter2.SelectCommand.Parameters.Add(parametre2)
verilaDataAdapter2.Fill(A1Dataset, "SarzKartTbl")
Dim p As DataColumn
Dim c As DataColumn
p = A1Dataset.Tables("StokKartTbl").Columns("StokID")
c = A1Dataset.Tables("SarzKartTbl").Columns("StokID")
iliski = New DataRelation("Elemanlar", p, c)
A1Dataset.Relations.Add(iliski)
DataGridStokKartTbl.DataSource = A1Dataset.Tables("StokKartTbl")
'Error
System.ArgumentException was unhandled
Message="Hata iletisi içeren isteğe bağlı bir kaynak derlemesi bulunamadığından bir hata iletisi görüntülenemiyor"
StackTrace:
System.Data.ConstraintCollection.AddForeignKeyConstraint()
System.Data.ConstraintCollection.Add()
System.Data.ConstraintCollection.Add()
at DataSetRelationCollection.AddCore()
System.Data.DataRelationCollection.Add()
AtisoftDA.siparis.Button2_Click()
System.Windows.Forms.Control.OnClick()
System.Windows.Forms.Button.OnClick()
System.Windows.Forms.ButtonBase.WnProc()
System.Windows.Forms.Control._InternalWnProc()
Microsoft.AGL.Forms.EVL.EnterMainLoop()
System.Windows.Forms.Application.Run()
AtisoftDA.siparis.Main()
View 1 Replies
View Related
Aug 26, 2007
HI Everyone.
how i can get the related tables.
my problem as example
i have a table name TT have relation with two table the first one TT1 and second TT2
i want to get the name of two table TT1 and TT2 by select statement.
thanks alot.
View 5 Replies
View Related
Jan 26, 2006
If I created relation between 2 tables without any constarins what will be the value of this relation or when SQL Server will use this relation
View 1 Replies
View Related
Oct 11, 2006
Why DataAdapter does NOT load relations from DB ?the relations are made using SQL SERVER MANAGEMENTbut when a fill a datatable using dataadapter the relations are not loaded .why ? what is the solution to this problem ?
View 1 Replies
View Related
Dec 21, 2003
i want to write program that will return
the relations between the tables and its type (one to many,.....etc)
any help
Thanks in advance
View 3 Replies
View Related
Feb 27, 2006
Are there tools for SQL2k database that take a database return allrelations of tables.thanks.Stefano.
View 4 Replies
View Related
Sep 19, 2000
Hi,
I have a doubt, can anyone please clarify me.
I have created the following table,
create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It's giving error,
create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno),
deptno char(2)
)
go
Error:-
*****
There are no primary or candidate keys in the referenced table 'z' that match the referencing column list in the foreign key 'FK__z_dup__eno__18CE1BA6'.Could not create constraint. See previous errors.
Even I tried to create a foreign key like the following,
create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno),
deptno char(2)
)
go
Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table 'z_dup'.
Can anyone please help me to create a foreign key relation with the base table.
tks in advance,
Srinivasan
View 5 Replies
View Related
Jan 30, 2007
I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables related with foreign keys. I can't change the way the local software uses primary or foreign keys as it is hardcoded in the local app. (microsoft retail management system)..(however the web-remote app is easily customized). I am using CDB synchronizer to sync the two databases because the remote one is mysql.
Example tables layout:
Items table has auto-increment primary key 'id'
TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'
Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'
locally the following products are added:
11001 short sleeve t---gets added with primary key in 'item' table 'id' of '7'
11002 long sleeve t----gets added with primary key in 'item' table 'id' '8'
remotely the following products are added:
21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'
31001 overalls---gets added with primary key in 'item' table 'id' '8'
remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,
product with "item_id" of '7' is "short sleeve t".
9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..
whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.
I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.
View 2 Replies
View Related
May 4, 2004
Hi all,
I have a big problem. I have many tables with constraints, with foreign keys. I need to create a ordered list of tables, on the top must be the basic table what has no parents, then the second level tables (those depends on the first level) the the names of third level etc.
for example:
Table A[id]
Table B[id, idc]
Table C[id, ida]
Table D[id, ida]
Table E[id, idc]
I tried it by using information_scheme but I was unsuccesfull.
The result should be:
A
C
D
B
E
Thank you,
Tom.
View 2 Replies
View Related
Jul 20, 2005
i've a database where relations are hold in a special way which the projectleaders think of as "performant and uncomplicated" but which is veryquestionable to me:------------------------------------------------Table [Attributes]Fields [AttributeID] and [AttributeText]Table [Objects]Fields object stuff.... and [AttributeIDs] (varchar with 0-20 ids usually)in AttributeIDs there is a backslash separted list of Attribute-IDs like'3412278'so to get 20 object with a special attribute (which we need often) we doSELECT TOP 20 *FROM ObjectsINNER JOIN AttributesON (Objects.AttributeIDs LIKE ('%' + (CAST AttributeID AS varchar) + '\%'))ORDER BY ObjectTextps: to store data we need for communication we include a dozen of fields in*every* table and its content makes about 100 bytes/record------------------------------------------------i would do this stuff with a table to store the object/attributecorrelations.could someone tell me if that stuff makes any sense to an expert and how tovaluate it in regard of performance(we have big customers where that *is* anissue), design, scalability, pragmatism and sense ;)thanks in advance,ViperDK
View 1 Replies
View Related
Sep 21, 2006
Hi,
I've been trying to export a whole database from one server to another (connected through a local network) but the keys always get lost: when I open the diagrame I can actually see them just before the tables names appear. I've tried inserting the primary keys for each table manually with the export wizard, which took me some time, but no relations were exported. Any way of doing this automatically?
View 5 Replies
View Related
Jul 19, 2005
Hi,after i exported my DataBase From my local computer to host, all tables and views have exported, but my tables Realations dosent exported and i have'not any realation in my database, i can't create new realation , when i want to do this i get an error:" You do not have sufficient privelege to create n new database diagram" What this mean??how i can export my database to my host whit my realations?plz help me i you can!thanks.
View 6 Replies
View Related
