Click here to Skip to main content
14,734,309 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
hi friends

i have a question about self referencing tables. first what is a self referencing tables

(Could you please give an example ) and second should i have two primary keys in a self referencing table? third how could i bind drop down list into a self referencing table?
for example a drop down for city and a drop down for province ? pleas help me .
Posted
Comments
Sergey Alexandrovich Kryukov 15-Oct-14 12:24pm
   
The meaning should be obvious from the name. :-)
—SA
vbmike 15-Oct-14 12:35pm
   
This link gives an example (albeit somewhat confusing) See if it makes sense to you then pursue more in google.....
http://www.w3resource.com/sql/joins/perform-a-self-join.php

First of all, you can never have two primary keys: http://en.wikipedia.org/wiki/Unique_key[^].

Don't confuse this statement with the situation when you can have a compound key, which can be uses as a primary key, but it still has to be unique: http://en.wikipedia.org/wiki/Compound_key[^].

In you use a unique compound key as a primary key, you still have only one primary key per table, even though such key uses more than one table columns.

As to "self-referencing tables", I don't think this is any special term or a notion. The existence of self-referencing tables is a trivial consequence of relational model. I have no idea what do you want to know about them and why. There is nothing wrong with self-referencing. I guess, it would be better to use your brain to analyze self-referencing situations. You can look here: http://bit.ly/1u5magb[^].

—SA
   
v5
Comments
   
I have tried to answer one question. My 5 for you. :)
Sergey Alexandrovich Kryukov 15-Oct-14 14:10pm
   
Thank you, Tadit.
—SA
[no name] 15-Oct-14 15:05pm
   
My 5 for the informative and important side notes. Regards, Bruno
Sergey Alexandrovich Kryukov 15-Oct-14 15:36pm
   
Thank you, Bruno.
—SA
Maciej Los 15-Oct-14 16:53pm
   
+5
Sergey Alexandrovich Kryukov 15-Oct-14 17:27pm
   
Thank you, Maciej.
—SA
Quote:
for example a drop down for city and a drop down for province ?
This is very simple. Two tables that's it.

1. City - CityId, CityName
2. Province - ProvinceId, FK_CityId, ProvinceName

While binding DropDownList for City, directly bind from City Table. The DropDownList for Province should be bound when some City is selected. So, you need to bind inside the SelectedIndexChanged Event of City DropDownList. You can also go for Ajax CascadingDropDowList. Refer - Ajax Cascading Dropdownlist Sample with database using asp.net[^]
Comments
Sergey Alexandrovich Kryukov 15-Oct-14 14:11pm
   
5ed.
—SA
   
Thanks Sergey. :)
[no name] 15-Oct-14 16:33pm
   
Member 11155615 at 28 mins ago

one question remains . when i wanna bind drop down list
should i youd selected value or selected index ? and how could i insert these data in two related tables

Tadit can you please help OP. I only know Little bit SQL ;)

Thanks, regards, Bruno
   
Sure Bruno. Thanks. :)
Maciej Los 15-Oct-14 16:53pm
   
+5!
   
Thanks Maciej. :)
A self referencing table is a table which has a field which is defined as foreign key to the same table's primary key.

CREATE TABLE DBREGISTRY
(
  ID		INTEGER,
  PARENT        INTEGER,    // PARENT references ID of table DBREGISTRY 
  DESCRIPTION   CHAR(30),
  INTVALUE      INTEGER,
  CONSTRAINT    PK_DBREGISTRY PRIMARY KEY (ID),
  CONSTRAINT	FK_DBREGISTRY_PARENT FOREIGN KEY (PARENT) REFERENCES DBREGISTRY (ID)
)


This kind of table does allow to save data rows in a hierarchical way.

ID    PARENET     DESCRIPTION
1     1           "ROOT"
2     1           "FIRST LEVEL ITEM A"
3     2           "ITEM 1 OF FIRST LEVEL ITEM A"
4     1           "FIRST LEVEL ITEM B"
5     4           "ITEM 1 OF FIRST LEVEL ITEM B"

For imaging the hirarchy:

"ROOT"
     "FIRST LEVEL ITEM A"
          "ITEM 1 OF FIRST LEVEL ITEM A"
     "FIRST LEVEL ITEM B"
          "ITEM 1 OF FIRST LEVEL ITEM B"


There are several things (some of them are pitfalls) to pay attention in praxis:
a.) For foreign key parent it makes sense to define also "ON DELETE CASCADE"
b.) Importing such a table is usually not an easy thing, this because items and there ID can be arise completely random.


E.g. : Windows registry seems to be made with a similar structure.
   
v5
Comments
Sergey Alexandrovich Kryukov 15-Oct-14 14:11pm
   
5ed for the detailed example.
—SA
[no name] 15-Oct-14 14:11pm
   
Thank you very much, regards Bruno
[no name] 15-Oct-14 14:57pm
   
And sorry to all who recognized every minute an update of this answer. I was fighting with the layout of this answer :-) Hope I know for next time how to write it in one piece.
Member 11155615 15-Oct-14 16:03pm
   
thank you so much . but one question remains . when i wanna bind drop down list
should i youd selected value or selected index ? and how could i insert these data in two related tables
[no name] 15-Oct-14 16:09pm
   
Thank you for accepting. For binding a drop down list please wait for the answer of Tadit Dash.

I only know the SQL part and have no big knowledge of the GUI part....sorry.
Bruno
   
To insert data to these tables, you have to make some other page or interface. Because these are master tables. So, you can make one page where you will have TextBoxes for a City Name and other additional TextBoxes for the provinces. When user enters all, then you execute query to database and first store the City in City Table. Get the Id of this inserted City. Now insert to the Province Table with the inserted City Id as a Foreign Key.

While binding the DropDowns, the Name should bind to as DropDown text and ID should bind as DropDown value. So, when some option is selected on DropDown, then inside the SelectedIndexChanged Event, just get the DropDownList SelectedValue, which is the Id. Then make a query with this Id and get the related table data and bind to the other DropDownList.
Member 11155615 15-Oct-14 16:18pm
   
tnx :)
Maciej Los 15-Oct-14 16:52pm
   
+5!
[no name] 15-Oct-14 16:55pm
   
Thank you Maciej. Regards, Bruno
   
My 5 as well. :)
[no name] 16-Oct-14 2:26am
   
Thank you. Regards, Bruno

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900