Generalization/specialization
Aug 2, 2007
I have a db design which includes generalization/specialization. How can I implement this in sql server?
thanks so much!
ADVERTISEMENT
Specialization Query Problem
Jun 18, 2005Two queries need to be joined such that the TSL-named column values are all unique. That is, only unique TSL values are returned in the result set (with the highest degree of specialization represented by ISO3166 - Country/Region Codes.)
Query 1 returns:
TSL ISO3166-1 ISO3166-2
x -- US
y -- US
Query 2 returns:
TSL ISO3166-1 ISO3166-2
x US-WA --
Desired join result:
TSL ISO3166-1 ISO3166-2
y -- US
x US-WA --
How do I create a query such that if a TSL value exists in both query 1 and 2 that is the same, x in this case, that the row from query 2 (US-WA is more specialized than US) is taken over query 1 results. The two queries are actually from the same table.
Accuracy Vs Generalization
Apr 15, 2006Hi!
So sorry for posting yet another one of my silly posts about the correct way of doing things!
I am using Stored Procedures to do various things in my database (inserting/deleting/updating) and using SQL Server 2000 SP4
The current situation is that I have a stored procedure which checks certain user details and returns either -1 or 1 as a success indicator to the caller.
Now, obviously if we executed several queries in this stored procedure, performance will be an issue.
However, I have no idea how to balance up the whole "more useful information" vs "general information". What I mean is this:
if we are checking 3 items in a table, I want to return the success value back to the caller - in this case, we could do an IF statement to see if the record exists, if it does, set the return value to 1, else, -1.
But then you may want to be more informative to the user, specifically stating what part of the information they entered is incorrect/invalid. So having this in mind, we would then need to execute, say, 3 queries to return a more specific "error" value.
What should I do in this case?
I want to check the username, password and if the account is activated.
Currently I have this query going on:
IF EXISTS (SELECT [ID] FROM Users WHERE username = @un AND [password] = @pw AND activate = 1)
SET @theResult = 1
ELSE
SET @theResult = -1
firstly, is that the good way of doing things? if not - then what is the better way?
secondly, if we decide that we want a specific detailed return value/error message, it would mean I have to check the results I want using a couple more queries, such as one query to check username, one query to check password and another to check if the account is active or not and then return the appropriate value back to the caller.
is this a good way of doing things? I am confused and stuck!
Many thanks for your valuable response :)
SQL For Modeling Generalization Hierarchies
Jul 20, 2005Is there a good approach to modelling many heterogeneous entity typeswith that have some attributes in common?Say I have entities "employees" which share some attibutes (e.g.firstname, lastname, dateofbirth) but some subsets of employees (e.g.physicians, janitors, nurses, ambulance drivers) may have additionalattributes that do not apply to all employees. Physicians may haveattributes specialty and date of board certification, ambulancedrivers may have a drivers license id, janitors may havepreferredbroomtype and so on.There are many employee subtypes and more can be dynamically addedafter the application is deployed so it's obviously no good to keepadding attributes to the employees table because most attributes willbe NULL (since janitors are never doctors at the same time).The only solution I found for this is a generalization hiearchy whereyou have the employee table with all generic attributes and then youadd tables for each new employee subtype as necessary. The subtypetables share the primary key of the employee table. The employee tablehas a "discriminator" field that allows you to figure out whichsubtype table to load for a particular entity.This solution does not seem to scale since for each value of"discriminator" I need to perform a join with a different table. Whatif I need to retrieve 1,000 employees at once?Is that possible to obtain a single ResultSet with one SQL statementSQL?Or do you I need to iterate look at the discriminator and thenperform the appropriate join? If this kind of iteration is necessarythen obviously this generalization hierarchy approach does not work inpracticesince it would be painfully slow.Is there a better approach to modelling these kind of heterogeneousentities with shared attributes that does not involve creating a tablefor each new employee type or having sparce tables (mostly filled withNULLS)I guess another approach would be to use name/value pairs but thatwould make reporting really ugly.Seems like a very common problem. Any ideas? Is this a fundamentallimitation of SQL?Thanks!- robert
View 13 Replies View RelatedPlease, How To Realize Generalization In Sql Server Database
Dec 12, 2006please help, I need professional approach how to realize generalization(parent_table <- child_table) idea in sql server databse?in my Oracle db I can have PK of child_table as FK from parent_table: let'shave shop with shoes, selling shoes, and foosd as well. Example of keys inmy tables:Product (=parent_table)-------------12345Food (=child_table)-------------234Shoes (=child_table)-------------15Is that good idea, or not, and may I have the same in SQLServer (2005) ?(and, what about administrative aspects (likeperformance/concurency/updating ..) of generalization in RDMS?)regards
View 1 Replies View Related