Can Someone Critique My Sql Relationship Structure?
Jun 6, 2008
I was building out my db and then ran into a problem updating a primary key value. I then started to doubt whether or not I even did this right. To simplify, I"m going to just create a smaller similar scenario and I just want to know if its right
Internet Table
CircuitID (PK)
Phonenumber(PK)
IP
SM
GW
now, Instead of creating a seperate column for just an incrementing numeric value as a PK i used the order number, phone number, and circuit id's as the Primary keys since every single value will be unique. Plus, I also did it to prevent duplicates. Now the problem I'm having is, when I do Update db set [phonenumber] = @phonenumber, ordernum = @ordernum, numbertype = @numbertype where phonenumber = @phonenumber as wrong as that looks.
It looks wrong to me.. problably why it doesn't work. How do you change the values of primary keys.. or should I have created a column called ID with incrementing numbers. How would I prevent duplicates then?
Here is a trigger I wrote to check for duplicate taxid's in my producer table. Duplicate taxid's with an '' value are allowed. I'm green when it comes to trigger coding so would anyone care to review my code and reply if there's a more efficient way to implement the trigger or handle errors.
Thanks PLJ
create trigger dbo.tib_producer on dbo.producer for insert as begin declare @numrows int, @errno int, @errmsg varchar(255) select @numrows = @@rowcount if @numrows = 0 return /* Check for duplicate tax_id if inserted is not null */ if update(tax_id) begin if exists(select tax_id from producer where tax_id in (select tax_id from inserted) and tax_id <> '' group by tax_id having count(*)>1) begin select @errno = 50002, @errmsg = 'Tax ID already Exists' goto error end
end return /* Errors handling */ error: raiserror @errno @errmsg rollback transaction end
I would really appreciate any constructive criticism of my sql script (see end of message).
I have been working for a week to pull data from a database that has evolved over the years. It might have been designed to begin with, but it has had a lot of changes made on the fly, which makes pulling some data from it very hard.
One example is that they wanted Primary Service for a given client but this data was not stored in a base client table, but was instead on a table with multiple rows per client. To pick the row that referenced the correct Primary Service I had to: 1. Join the Client table with the view on client number and primary program number 2. From the records returned look at another column (proc_cde) in the view and pick the one that had a higher priority than the others. The precedence order of the proc_cdes was determined by a list my boss gave me. I used this forum to help me figure this part out.
Anyway … I finally got it done and it works … but just because it works doesn't mean it is well written. What I would like is if some of you could take a look at the sql and critique it for good and/or bad practices.
The design of the database is a given (and not my doing) so I am not looking for a critique of it.
An Example of what I am asking for would be: I ended up using temp tables to collect some of the more complicated data so that I could write a very straightforward final query. Was this a good or bad thing and if bad why?
I really want to get good at sql and avoid developing any bad habits, so please critique my sql.
Also please ask me any questions that could help you evaluate the code.
Thanks,
Laurie
Note: the comments at the top were all the specs I got for creating this report (they came in an email).
/*detail- primary clincian/provider priemp_num joined to facemp Y primary service (act,cm,opt), pripgm_num primary program, select proc_cde from v_autsvc join svc on proc_cde where pgm_num=pripgm_num H0039 then T1017 t1016 then H0036 location of service, pgm_num of 90862 auth date of auth v_autsvc.aut_dte cons name/number: CLTCLT auth date range v_autsvc.autbeg_dte v_autsvc.autend_dte number of claims in the last fiscal year v_clmsvc, count(*) primary psycchiatrist. cltdmo.pspemp_num join phy on phy_num
select clt_num, count(*) as num_claims from v_clmsvc where beg_dte between '10/1/2005' and '9/30/2006 23:59' group by clt_num
tables: v_autsvc, pgm, cltdmo.population='mi adult' proc_cde in ('90862','90801') autend_Dte>'10/1/06'
I need a report showing all Adult MI consuemrs receiving psych services by agency- like the aggregate and detail of who gets them at where-bridgeways, sr srv... including our clinic.
i would need in the detail- primary clincian/provider, primary service (act,cm,opt), location of service, date of auth, cons name/number, auth date range/# of units and number of claims in the last fiscal year and primary psycchiatrist. if xxx asks for something similar, let me know as we might be duplicating.
i know that xxx is going to be asking for a report that is for just our clinic that has more to do with insurance type. if it would be easier, you could combine the 2 reports as long as we have the capacity to sort and print by provider. thanks*/
/********************************************************************************** Primary Service ***********************************************************************************/ -- select primary proc_cde for primary service Candidates drop table #ConsumerProcCodes select distinct a.clt_num, a.proc_cde, a.aut_dte, a.autbeg_dte, a.autend_dte into #ConsumerProcCodes from cltdmo d join v_autsvc a on a.clt_num = d.clt_num and d.pripgm_num = a.pgm_num and a.autsts = 1 and getdate() between a.autbeg_dte and a.autend_dte
-- create table of ProcCde Precedence drop table #ProcCde_Precedence create table #ProcCde_Precedence (proc_cde varchar(10), Precedence int)
insert #ProcCde_Precedence (proc_cde,Precedence) select 'H0039', 10 union all select 'T1017', 20 union all select 'T1016', 30 union all select 'H0036', 40 union all select 'T2011', 50 union all select '90806', 60 union all select '90862', 70 union all select 'T1002', 80 union all select 'H2031', 90 union all select 'H2023', 100 union all select 'H2015', 110 union all select 'T1005', 120
-- assign precedence to proc_cde drop table #ConsumerProcCodesWPrec select distinct c.clt_num, c.aut_dte, c.autbeg_dte, c.autend_dte, case when p.Precedence is null then 1000 else p.Precedence end as Precedence, left(c.proc_cde,5) as proc_cde into #ConsumerProcCodesWPrec from #ConsumerProcCodes c left join #ProcCde_Precedence p on p.proc_cde = left(c.proc_cde,5)
-- select primary proc_cde for each consumer and get associated PrimaryService drop table #PrimaryService select distinct p1.clt_num, p1.proc_cde, p1.aut_dte, p1.autbeg_dte, p1.autend_dte, s.acttyp_des as PrimaryService into #PrimaryService from #ConsumerProcCodesWPrec p1 join svc s on p1.proc_cde = s.proc_cde where Precedence = (select min(Precedence) from #ConsumerProcCodesWPrec p2 where p1.clt_num = p2.clt_num)
-- Find Consumers with more than one primary Proc_cde /* select clt_num, proc_cde, aut_dte, autbeg_dte, autend_dte from #PrimaryService where clt_num in (select clt_num from #PrimaryService group by clt_num having sum(1) > 1) */ /********************************************************************************** Primary Location ***********************************************************************************/ -- select pgm_num for Primary location Candidates drop table #ConsumerLOS select distinct a.clt_num, a.pgm_num, p.pgm_nme into #ConsumerLOS from v_autsvc a join pgm p on a.pgm_num = p.pgm_num where a.autsts = 1 and getdate() between a.autbeg_dte and a.autend_dte and a.proc_cde = '90862'
-- create table of pgm_num for Location of Services Precedence drop table #LOS_Precedence create table #LOS_Precedence (pgm_num int, Precedence int)
insert #LOS_Precedence (pgm_num,Precedence) select 5600, 10 union all select 6200, 20 union all select 6000, 30 union all select 1611, 40 union all select 1612, 50 union all select 1601, 60
-- assign precedence to pgm_num drop table #ConsumerLOSwPrecedence select distinct c.clt_num, case when p.Precedence is null then 1000 else p.Precedence end as Precedence, c.pgm_num into #ConsumerLOSwPrecedence from #ConsumerLOS c left join #LOS_Precedence p on p.pgm_num = c.pgm_num
-- select pgm_num for primary location of Services for each consumer drop table #PrimaryLOS select distinct p1.clt_num, case n.NumRows when 1 then cast(p1.pgm_num as varchar(10)) else cast(p1.pgm_num as varchar(10)) + '*' end as LocationOfService into #PrimaryLOS from #ConsumerLOSwPrecedence p1 join (select clt_num,count(*) as NumRows from #ConsumerLOSwPrecedence group by clt_num) n on p1.clt_num = n.clt_num where Precedence = (select min(Precedence) from #ConsumerLOSwPrecedence p2 where p1.clt_num = p2.clt_num)
-- Find Consumers with more than one primary pgm_cde for Location of Services. /*select distinct clt_num from #PrimaryLOS select * from #PrimaryLOS where clt_num in (select clt_num from #PrimaryLOS group by clt_num having sum(1) > 1)*/
/********************************************************************************** Put it all together ***********************************************************************************/ drop table #FinalResults select distinct c.clt_num, c.fst_nme + ' ' + c.lst_nme as ConsumerName, f.fst_nme + ' ' + f.lst_nme as PrimaryClinician, p.fst_nme + ' ' + p.lst_nme as PrimaryPsychiatrist, ps.PrimaryService, COALESCE(n.num_claims,0) as num_claims, los.LocationOfService, ps.aut_dte, ps.autbeg_dte, ps.autend_dte into #FinalResults from cltctl c join cltdmo d on c.clt_num = d.clt_num and d.population = 'mi adult' join v_autsvc a on c.clt_num = a.clt_num and a.autsts = 1 and getdate() between a.autbeg_dte and a.autend_dte and a.proc_cde in ('90862','90801') join facemp f on d.priemp_num = f.facemp_num left join phy p on d.pspemp_num = p.phy_num left join #PrimaryService ps on ps.clt_num = c.clt_num left join (select clt_num, count(*) as num_claims from v_clmsvc where beg_dte between '10/1/2005' and '9/30/2006 23:59' group by clt_num) n on c.clt_num = n.clt_num left join #PrimaryLOS los on c.clt_num = los.clt_num order by c.clt_num
select * from #FinalResults /* select * from #FinalResults where PrimaryService is null
select * from #FinalResults where LocationOfService is null
select * from #FinalResults where PrimaryPsychiatrist is null
select * from #FinalResults where PrimaryClinician is null
I just finished a new query where I summarized detail information. I'mwondering if I did this really awkwardly or is this a common way towrite SQL? I've cross referenced the end results and the data seemsconsistant, so I am happy with the results.TIASELECTSESSION_ID,CAMPUS_ID,SUM(STUDENT_COUNT) AS STUDENT_COUNT,SUM(NEW_STUDENT) AS NEW_STUDENT_COUNTFROM (SELECTSESSION_ID,STUDENT_ID,CAMPUS_ID ,STUDENT_COUNT ,STUDENT_STARTING_SESSION_ID,NEW_STUDENT = CASE WHEN SESSION_ID=STUDENT_STARTING_SESSION_IDTHEN (1) ELSE (0) ENDFROM (selectSESSION_ID,STUDENT_ID,CAMPUS_ID ,STUDENT_COUNT ,STUDENT_STARTING_SESSION_IDFROM(selectSESSION_ID,STUDENT_ID,CAMPUS_ID = (SELECT STUDENT_CAMPUS_ID FROMD_BI_STUDENT WHERE A.STUDENT_SKEY=D_BI_STUDENT.STUDENT_SKEY) ,STUDENT_COUNT = DAY0_CLASS_COUNT,(select student_starting_session_id fromf_bi_student_statistics where A.student_id =f_bi_student_statistics.student_id) as 'STUDENT_STARTING_SESSION_ID'from f_bi_registration_tracking_summary A) AS XWHERE STUDENT_COUNT > 0GROUP BY SESSION_ID, STUDENT_ID, CAMPUS_ID, STUDENT_COUNT,STUDENT_STARTING_SESSION_ID) AS Y) AS ZGROUP BY SESSION_ID, CAMPUS_ID
This is my 1st attempt at designing a database, and I have not finished it completely, but I would like somebody to review and critique for me. I really don't want to make any mistakes with this and I would appreciate any expertise out there to direct, recommend, suggest improvements and/or changes, PLEASE.
Thank you for considering this,,,if you provide me with your e-mail, I can send you a script.
All: This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.
I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh
How can I set a one-to-one relationship using the Management Studio Express and SQL Server 2005 Express tblClient, CleintID (PK) tblProcess, ClientID (FK)
Hello I have need to write a query that I can pass in a bunch of filter criteria, and return 1 result....it's just ALL of the criteria must be matched and a row returned: example: Transaction table: id, reference attribute table: attributeid, attribute transactionAttribute: attributeid, transactionid Example dat Attribute table contains: 1 Red, 2 Blue, 3 Green Transaction table contains: 1 one, 2 two, 3 three transactionAttribute contains: (1,1), (1,2), (1,3), (2,3), (3,1)
If I pass in Red, Blue, Green - I need to be returned "one" only If I pass in Red - I need to be returned "three" only If I pass in Red, Green - nothing should be returned as it doesn't EXACTLY match the filter criteria
If anyone's able to help that would be wonderful! Thanks, Paul
How to create a relation between gf_game and gf_gamegenre here? gf_gamegenre is responsible for the relation between a game and it's genre(s). The relationship between gf_genre and gf_gamegenre worked. (http://img361.imageshack.us/my.php?image=relationzl9.jpg)
When I try to set a relationshop between gamegenre and game I'm getting this error: 'gf_game' table saved successfully'gf_gamegenre' table- Unable to create relationship 'FK_gf_gamegenre_gf_game'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_gf_gamegenre_gf_game". The conflict occurred in database "gamefactor", table "dbo.gf_game", column 'gameID'. Thanks for any help!
Can anyone provided insight on how to create a one-to-one relationship between SQL tables? Every time I try to link two tables that should be one-to-one, the link says one-to-many. How can I specify one-to-one when SQL Server automatically thinks it is a one-to-many? Thanks, Kellie
Hey, I know I'm asking a stupid question but I need to get a clear response please: why using One-to-One relationship instead of meging the 2 tables in only one? thanks.
I created 2 tables with one to one relationship. if I add a record intable A, how does table B record get created? Does SQL do thisautomatically because it is one to one relationship? or do I need tocreate a trigger? if i need a trigger, how do I get the ID of newrecord to create the same ID in table B?thanks for any help.Joe Klein
Hi,Do you guys know what's wrong with a one-to-one relationship?The reason I want to make it like this is that at the very end of the chain,the set of keys is huge. I want to limit the number of columns to be thekey. i.e. the [company] table has 1 column as the key. The [employee]table will have 2 columns as the key.e,g,If I add a [sale] table to the [company]-[employee] relationship, the thirdtablewill have 3 columns as the key -- "company id", "employee id", and "saleid".(e.g.)I have a company with many employees and computers. But instead of classifyall these, I just want to call all these as an entity. A company is anentity. An employee is just another entity. etc.So, instead of a one-to-many:[company]---*[employee]---*[sale]||*[computer]I make it one-to-one.[entity]---*[entity]If I want to know the name and address of the entity "employee", I will havea 1-to-1 table [employee] to look up the information for this employeeentity.[entity]---*[entity]||[company]||[employee]||[computer]||[sale]--[color=blue]> There is no answer.> There has not been an answer.> There will not be an answer.> That IS the answer!> And I am screwed.> Deadline was due yesterday.>> There is no point to life.> THAT IS THE POINT.> And we are screwed.> We will run out of oil soon.[/color]
How do I create a one to one relationship in a SQL2005 Express database? The foreign key needs to be the same as the primary key so it can't just increment to the next number.
Hi. I get this error when i try to create a relationship in a db diagram (sql 2005) "'tblActivedir' table saved successfully 'tblClient' table - Unable to create relationship 'FK_tblClient_tblActivedir1'. Introducing FOREIGN KEY constraint 'FK_tblClient_tblActivedir1' on table 'tblClient' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors."
What i have is 2 tables. 1 named client 1 named activedir
In the client table the columns i want to bind with activedirtable are FR1 and DC1 I want to bind them in the ID of the activedir table (both, in different fk relationships) so that they get the id of activedir. Fr1 has an fk relationship with activedir (pk is activedir' id) and DC1 exactly the same in another fk. So i want both columns to comunicate with activedir. If p.e. activedir has 3 elements (a,b,c) when i delete element a then werever FR1 or DC1 have this element(binded to it's id) then the element will also be deleted (id of the element) from both FR1 and DC1 I don't want to set Delete and Update action to none because i want the element changed or deleted from activedir, to do the same on Fr1 or DC1 or both. Any help? Thanks.
I am trying to create a 1:1 relationship, but not primary key to primary key. In table 1 I have a uniqueidentifier as a primary key. In table 2 I have an int as the primary key and a column that takes the uniqueidentifier from table 1. Everytime I drag and drop the relationship line and link table 1 to table 2 it creates a 1:N relationship: ie. tbl1.primarykey links to tbl2.column2. So I'm not linking primary key to primary key however I still want a 1:1 relationship.
SQLServer 2005 - I have two tables. One has a field defined as a Primary Unique Key. The other table has the same field, but the Index is defined as non-Unique, non-clustered. There is no primary key defined on the second table. I want to set up a one-to-many relationship between the two, but am not allowed.
This should be simple. What am I doing incorrectly?
This is the message that i get when trying to assign keys when creating diagrams in visual express:
'tbh_Polls' table saved successfully 'tbh_PollOptions' table - Unable to create relationship 'FK_tbh_PollOptions_tbh_Polls'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tbh_PollOptions_tbh_Polls". The conflict occurred in database "C:USERSSTICKERDOCUMENTSMY WEB SITESPERCSHARPAPP_DATAASPNETDB.MDF", table "dbo.tbh_Polls", column 'PollID'.
PollID is my primary key in tbh_Polls
And PollID is in tbh_PollOptions table
No matter what I do, I get this message, I'm Lost!
I have a fact table with 2 fields : "Dim Code 1" and "Dim Code 2" that I want to link with a Dim table. I don't want to create two dimensions Dim1 and Dim2 but only one dimension with something like :
Shirt shirt_id client_id meas_arm meas_neck meas_shoulder color_code description My question Is it possible to have a relationship linking one single table to other several one. For example i wanted to relate the field client_id from table client which is the primary to tables shirt,trousers and smoking with the client_id field which is the foreign key ?
Hi there everyone, this is my first post so go easy on me :) Basically I am trying to get my database to copy the value in the UserId (unique identifier field) from the aspnet_Users table to a foreign key UserId in a table called userclassset. I have made this field the same datatype and created a relationship between the two. Unfortunately, when I add a user using the ASP.Net configuration tool it does not automatically copy this value into my own custom table. I have noticed it is however automatically copied into the aspnet_Membership table. Any pointers on how to solve this would be great! Thanks :)
Hi, how can i make optional relationship? for example: In table A, there is column 1, column 2, column3. In table B, there is column 4, column 5 and column 6. column 1 and column 2 are primary keys for table A and table B. The relationships between table A and table B are column 2 and column 5; column3 and column 6. but optional (ie. when data exists in column 2, then column3 is null) how can i set the relationship? because one of the columns data is null each time, error always occurs.
Hello, I created some SQL 2005 tables using Microsoft SQL Server Management Studio. I need to get the script code of those tables. I was able to do that by right clicking over each table. But how can I get the code for the relationships between the tables? Can't I create relationships between two tables by using T-SQL? Thanks, Miguel
When I try to insert a record on my DB (SQL 2005 Express) I get a constraint error. This is my table setup which has been simplified to expose the problem I have: Categories TABLEint CatId PKvarchar CatName : Items TABLEint ItemId PKvarchar ItemName : X_Items_Categoriesint CatIdint ItemId So basically I have a one-to-many relationship between Items and Categories, in other words each item is associated to one or more categories and this association is done via the X_Items_Categories cross table. On this cross table I set two constraints:
The CatId of each entry in the cross table (X_Items_Categories) must exist in the Category table, and
Hi there, I just set up my AppData and i'm trying to connect the Membership to a tbl_Profile that i created.I notice there's an application_id and a user_id which are uniqueidentifiers. I've been trying create a relationship to tbl_Profile's user_id column but it won't let me saying it's not compatible. Am i suppose to set tbl_Profile's user_id column to int or uniqueidentifier? I've tried both and it won't let me... THanks!
HiI have 2 tables one is called QuickLinks and another called QuickLinkItemsQuickLinksQuickLinkID<PK> UserID QuickLinkName-------------------------- ----------- ------------------------1 111-111-111-111 QuickLink12 111-111-111-111 QuickLink23 222-222-222-222 QuickLink1 QuickLinkItemsQuickLinkID <FK> CharacterName CharacterImagePath---------------------- ------------------------------- ---------------------------------------- 1 a path11 b path22 d path32 e path43 a path14 b path 2 So thats how I see the data will look like in both of these tables the problem however I don't know how to write this into Ado.net code. Like I have made a PK to FK relationship on QuickLinkID(through the relationship builder thing). I am just not sure how to I do this. For the sake of an example say you have 5 text boxes 2 text boxes for inserting the character names and 2 text boxes for inserting the CharacterImagePath. Then one for the Name of the QuickLink. So how would this be done? Like if it was one table it would be just some Insert commands. Like if this was all one table I would first get the userID from the asp membership table then grab all the values from the textBoxes and insert them where they go to and the QuickLinkID would get filled automatically. The thing that throws me off is the QuickLinkID since it needs to be the same ID as in the QuickLink table. So do I have to first insert some values into the QuickLink table then do a select on it and grab the ID then finally continue with the inserts Into the QuickLinkItems table? Or do I have to join these tables together?Thanks
