InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 02:54:27 GMT20http://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspxThere are object-oriented database engines available. Cache is one of them, for example. They have the OO properties that you are looking for. If you absolutely have to have polymorphism in a database, go with an OODBMS (Bing/Google that term if you don't know it), not with an RDBMS.It is theoretically possible to do inheritence and polymorphism in an RDBMS. But you can't attach methods to an object, and columns aren't really properties. OODBMSs allow you to do proper inheritence, polymophism, properties, and methods. That's what they're for. They tend to be less effective at the things RDBMSs are good at, like data analysis and reporting, data mining, set-based queries, and so on. But they are good for OO data storage.Thu, 27 Dec 2012 10:33:10 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspxThanks alot of your instructions.i find my answer.Thu, 27 Dec 2012 10:15:23 GMTtz.bahramihttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspx[quote][b]tz.bahrami (12/27/2012)[/b][hr]How about polymorphism in sql? Could we use stored procedures for polymorphism?And for nested relationships can we define an attribute as OId and use it in a select query to point to the table that we want use it in our nested relationship?[/quote]Once again... SQL Server is not an object database. T-SQL is not an object orientated language.While you may be able to force some OO-like features into a DB, you're very likely going to end up with a poor design and a badly performing database as a result. Keep the OO in the front end, where your language is OO and not in the database.Thu, 27 Dec 2012 10:08:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspx[quote][b]tz.bahrami (12/27/2012)[/b][hr]Thanks alot.How about [b]polymorphism[/b] in sql? Could we use stored procedures for polymorphism?And for nested relationships can we define an attribute as OId and use it in a select query to point to the table that we want use it in our nested relationship?[/quote]It depends on what you mean by that and how you want to class your entities. In the context of a database you could contrive a qualifying description of polymorphism, but in general, no. Relational database theory is built on the notion of a [u][url=http://en.wikipedia.org/wiki/Relation_(database)]relation[/url][/u] (manifested as a table in an RDBMS) representing a specific entity, and only one. In other words you would not store cars and trees in the same table even though they both inherit from 'System.Object' (drawing on .NET object hierarchy). You might store different models of cars or species of trees where car inherits from [i]vehicle[/i] and tree inherits from [i]plant[/i]. Or you might want a vehicle table with properties common to cars, trucks, airplanes, etc. with child tables for each of those I mentioned. It depends on how granular you want to get.For example say you had a table that stored types of trees (named tree) and in that table you wanted to store a deciduous tree and an evergreen. You might have a column named 'max_recorded_height' and another named 'average_life_expectancy.' However, you may [i]not[/i] want a column called 'average_needle_length' because deciduous trees do not have needles, only evergreens do. So, you may want to create a child table to [i]tree[/i] called [i]evergreen[/i] that would only be referred to by objects of type [i]evergreen[/i] (who inherit from your [i]tree[/i] object) that was instantiated within your application so it could fill those properties that only pertained to evergreens. That said, you would still look into having those properties normalized out of the [i]tree[/i] table since not all trees will have needles.I would suggest you do some reading on normalization, especially normal forms, start with 3NF, and read the article about the impedance mismatch I linked to above.Thu, 27 Dec 2012 09:31:28 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspx[quote][b]tz.bahrami (12/27/2012)[/b][hr]Thanks alot.How about polymorphism in sql? Could we use stored procedures for polymorphism?And for nested relationships can we define an attribute as OId and use it in a select query to point to the table that we want use it in our nested relationship?[/quote]HUH???? How can a stored procedure be used to implement polymorphism? sql is NOT an OO programming language. It is a data storage system. It is not ever going to be this type of OO entity that you are talking about.Thu, 27 Dec 2012 09:19:54 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspxThanks alot.How about polymorphism in sql? Could we use stored procedures for polymorphism?And for nested relationships can we define an attribute as OId and use it in a select query to point to the table that we want use it in our nested relationship?Thu, 27 Dec 2012 09:09:45 GMTtz.bahramihttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspxYou can create views to "merge" two tables but they get tricky when you try to update columns from both tables.You could create a wide table (sparse) containing all the sub-types as nullable columns and implement views on it to give you your subset that you can update.Thu, 27 Dec 2012 08:32:55 GMTBill Taladahttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspx[quote][b]tz.bahrami (12/27/2012)[/b][hr]Thanks for your instruction, i am familiar with junction table, but i look a way to implement something like inheritance. In addition i agree with you for encapsulating by stored procedures.[/quote]Your request still doesn't make sense. If you MUST think of sql as objects think of the table as the object and each row as the instance and each column is the property. What you are describing is you want to inherit the row. This doesn't make sense because in OOP that would be like inheriting the properties of an instance. It doesn't work like that. You inherit the object.I think that what you are really trying to do is to have a parent - child relationship in sql. This of course is not only possible, it is a fundamental base for normalized data.Is the following example something along the lines of what you talking about?[code]create table #Parent( ParentID int identity, SomeValue varchar(10))create table #Child( ChildID int identity, ParentID int, SomeValue varchar(10))insert #Parentselect 'Parent 1' union allselect 'Parent 2'insert #Childselect 1, 'Child 1' union allselect 1, 'Child 2' union allselect 2, 'Child 3' union allselect 2, 'Child 4' union allselect 2, 'Child 5'select * from #Parent pjoin #Child c on p.ParentID = c.ParentIDdrop table #Parentdrop table #Child[/code]Thu, 27 Dec 2012 08:32:13 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspxThanks for your instruction, i am familiar with junction table, but i look a way to implement something like inheritance. In addition i agree with you for encapsulating by stored procedures.Thu, 27 Dec 2012 08:20:43 GMTtz.bahramihttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspx[quote][b]tz.bahrami (12/27/2012)[/b][hr]In inheritance a child table can gain father's features, so if i add child table's primary key to father's table as foreign key, it can gain father's features it is like inheritance.is'nt it?[/quote]That would limit you to one child. If you make a "join" table between them, it will allow you to have many children or parents. A join table row would contain keys for the parent and child.Once again...if you use stored procedure calls you can encapsulate everything, hide implementation, and have loose coupling. Think of stored procedures as method calls.Thu, 27 Dec 2012 07:09:46 GMTBill Taladahttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspxIn inheritance a child table can gain father's features, so if i add child table's primary key to father's table as foreign key, it can gain father's features it is like inheritance.is'nt it?Thu, 27 Dec 2012 02:49:15 GMTtz.bahramihttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspxHere is some available reading on the topic that may help you gain a deeper understanding of what things to consider before implementing your database:[u][url=http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch]Object-relational impedance mismatch, From Wikipedia[/url][/u]Wed, 26 Dec 2012 16:30:09 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspxOnly expose stored procedures as the interface to the front end. Do whatever you like behind the scenes. It is about as object oriented as you can get with a database.Wed, 26 Dec 2012 14:14:23 GMTBill Taladahttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspx[quote][b]tz.bahrami (12/26/2012)[/b][hr]I know features of object orientation could not implemented, but i want to find a way to implement them.[/quote]Only advice I can give you there is don't. SQL is not an object database. You may be able to force some object stuff into the DB if you hammer hard enough, doesn't make it a good idea.Wed, 26 Dec 2012 13:13:34 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspx[quote][b]tz.bahrami (12/26/2012)[/b][hr]I know features of object orientation could not implemented, but i want to find a way to implement them. For example for implementing inheritance we can use foreign key.Please help me to find some ways for encapsulation and other features.[/quote]Can you rephrase your question? I don't understand what you are asking here at all.How can you a foreign key to implement inheritance?Wed, 26 Dec 2012 13:09:46 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1400347-361-1.aspxI know features of object orientation could not implemented, but i want to find a way to implement them. For example for implementing inheritance we can use foreign key.Please help me to find some ways for encapsulation and other features.Wed, 26 Dec 2012 13:01:45 GMTtz.bahrami