Question For --CELKO--
Jul 20, 2005
Greetings,
I think I saw an article yesterday in which you told someone not to make
an Identity column a primary key. I seem to remember you saying
something like "It undermines the entire foundation of the relational
model."
I've been thinking about that all night and I think I understand what
you're saying, but I have some questions.
I think you're saying that if the real key in a 3 attribute (heh, heh.
didn't say fields. heh heh) tuple (heh heh) is a combination of the
three attributes, then that's what you should use as a primary key.
Do you then advocate never using an Identity attribute? Or is it
acceptable (in the relational model) to have an Identity attribute to
use as a handle to the row, and for attributes in other tables to use as
the target for a foreign key?
Thank you,
-- Rick
View 18 Replies
Jul 20, 2005
hello i have implemented joe celko's model to store heirarchy and itworks well and nicei have a question about SQLthis is the actual tablemember side left right------------------------------------------nancy L 1 36andrew L 4 21steven R 5 12ina L 6 7david R 10 11margaret L 13 20ann R 14 15laura L 18 19janet R 24 35michael L 25 30dan R 26 27ron L 28 29robert R 33 34the Side column is to tell its left, or right. this is a binaryheirarcy.i have this problem i have to solve, im still banging my head. Ifgiven the member'Nancy' , i need to find left-most(Laura) and right-most(Robert)'Janet' = left most is ron, right most is robert'Andrew = left most is laura, right most is DavidHope u get my plan. could u help me with the sql ?
View 3 Replies
View Related
Jul 3, 2006
Hi,
With reference to http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427
I want the "sql stmt" which wud give the lft and rgt col values..
i am reading his book but cant understand :eek: where he explains
wat lft and rgt cols are..
"The root is always (lft,rgt) (1, 2*(Select count(*)from table) and leaft nodes are (lft+1=rgt)" :S
View 3 Replies
View Related
Feb 9, 2004
Hi,
I am trying to revamp our product database with a view to making it search-optmised and would like some guidance (or confirmation of method, if you will!!). We currently use a three table structure (Product, Brand, Cat(egory)) along the lines of :
create table product
(prod_id int not null,
brand_id int not null,
cat_id int not null,
other stuff e.g. tech. specs, displayed text on web page, etc....
)
with corresponding brand_id and cat_id in the other tables. While this seems relationally sound I see it as being inefficient for searching, particularly after reading the theory behind nested sets.
A new function I am building will enable users to drill down through the product list or runs searches against all or part of the db :
e.g. all products from one category,
all products fitting certain search criteria,
products from several selected brands fitting certain criteria,
and any combination of the above you can think of!
The problem is, not all products have the same criteria list (in fact I would be surprised if any did) and may also be of more than one category (a digital camera with movie mode might easily fit into the digital camcorder search). I think I am correct in that a nested set would make the structure fit the requirement - things like criteria, displayable text, etc. could be nodes in their own right and each logical level might have its own criteria. For example, if a category is selected then certain text must be displayed and could list further categories or products. The next level down would then require its own displayable text - I am mainly thinking about SEO tags here. Also, I am not precious about retaining the current table structure and would like an open ended solution where I can add further data/functionality in a dynamic fashion, which nested sets seem to embody.
Does this make sense to anybody coz I think I've confused myself even more!!
Thanks in advance,
G
View 14 Replies
View Related
