Home » SQL & PL/SQL » SQL & PL/SQL » Nested table or ref.. or what else?
Nested table or ref.. or what else? [message #193447] Sat, 16 September 2006 16:32 Go to next message
daad
Messages: 10
Registered: September 2006
Junior Member
hello, first of all i don't know if it's the right discussion forum to post this question to, but couldn't find anything better.
I'm very new to oracle, it's about a week i'm reading around tutorials, manuals etc... but still can't start my project yet.
I have to handle restoraunts of some cities. Every cities (name,province) is divided in some areas(id, name, city). every restaurants (id, name) is placed in an area [where the attributes underlined are primary key]
Now, to model this situation i thought to create a table Cities with name,province,areas where areas is a nested table with id,name as attributes. Then create a Restaurants table with id,name,located attributes where located is a ref attribute that point to a row of the nested table on a row of Cities. Is it possible?
The first problem i saw is that if i insert in a nested table 2 row with same id, no errors occur, but for every cities can't be 2 areas with same id, so that is not correct: is there a way to set a primary key on nested table?
Plus i tried several query to try to ref the attribute located in table Restaurant to a row of the nested table of a row of Cities table, but couldn't succed.
Am i using the wrong approach for this case?

Sorry if it's not the right sections of discussion forum, in case please forward me where i can post a question like that.
regards
Re: Nested table or ref.. or what else? [message #193448 is a reply to message #193447] Sat, 16 September 2006 17:32 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
I suggest that you use GOOGLE to do some reading & research on "Third Normal Form".
For me & others, what exactly do you mean by the term "nested table"?
Re: Nested table or ref.. or what else? [message #193454 is a reply to message #193448] Sun, 17 September 2006 03:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
anacedent wrote on Sun, 17 September 2006 00:32

I suggest that you use GOOGLE to do some reading & research on "Third Normal Form".
For me & others, what exactly do you mean by the term "nested table"?

I suggest that you use GOOGLE to do some reading & research on "Oracle Nested Tables" Wink

@daad, have you considered an old fashioned relational datat model? Are the necessary constraints in place?

MHE
Re: Nested table or ref.. or what else? [message #193457 is a reply to message #193447] Sun, 17 September 2006 04:18 Go to previous messageGo to next message
daad
Messages: 10
Registered: September 2006
Junior Member
I have to use as much as possible the Object Relational functions. I thought to do something like that:

City(name, province)
Areas(id, name, city(ref))
Restaurants(id, name, placed_in(ref))

where (ref) means that the attribute is a ref to a row of the table above, since every restaurants is placed in one area, and each area is placed in one city. Is it a good approach?
The only problem i have is that i can't specify a ref attribute as primary key, so if i try:
CREATE TABLE Areas ..... (PRIMARY KEY (id, city) ENABLE) ....

would return an error.
So how can i check the limitation that for every city can't be two areas with same id?
Re: Nested table or ref.. or what else? [message #193459 is a reply to message #193457] Sun, 17 September 2006 04:45 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps something like this?
CREATE TABLE province
(id       NUMBER PRIMARY KEY,
 name     VARCHAR2(20));
 
CREATE TABLE city
(id       NUMBER PRIMARY KEY,
 name     VARCHAR2(20),
 province NUMBER REFERENCES province (id));
 
CREATE TABLE areas
(id       NUMBER PRIMARY KEY,
 name     VARCHAR2(20),
 city     NUMBER REFERENCES city (id));
 
CREATE TABLE restaurants
(id       NUMBER PRIMARY KEY,
 name     VARCHAR2(20),
 area     NUMBER REFERENCES areas (id));
Re: Nested table or ref.. or what else? [message #193460 is a reply to message #193459] Sun, 17 September 2006 05:18 Go to previous messageGo to next message
daad
Messages: 10
Registered: September 2006
Junior Member
no, because i can create two areas with same ID, but need to be refered to two different cities, you got my point? I mean, setting primary key only to id of table Areas, this would avoid to create two areas with same id, even if it's allowed. The thing i need is that two areas with same id are not refered to same city.
Re: Nested table or ref.. or what else? [message #193536 is a reply to message #193460] Mon, 18 September 2006 02:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
But in a good design, your primary key shouldn't hold any information - it should just be a unique value to address and reference that record by.
You seem to be imbuing the ID of the AREA table with some special meaning - this is generaly a bad plan, as it causes a lot of trouble when that meaning has to change.
Re: Nested table or ref.. or what else? [message #193548 is a reply to message #193447] Mon, 18 September 2006 03:32 Go to previous messageGo to next message
daad
Messages: 10
Registered: September 2006
Junior Member
the id of the area is supposed to be strictly linked with the city, since a city has only areas with unique ids, while in the database can exists more areas with same id, the only limitations is that two areas with same ids can't be linked to same city.
That's can't be handle without adding any other unique identifiers?
Re: Nested table or ref.. or what else? [message #193555 is a reply to message #193548] Mon, 18 September 2006 04:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Exactly. These IDs that you are using sound like poor candidiates for primary keys. They hold information, and I suspect that they are subject to change - when city areas are redefined, for example.
I would make this ID of yours another column of the table, and use a sequence generated number for the primary key.

Other than that, you'll have to bring the city id down into the Area table, and have a primary key of City_id, Area_id.
Re: Nested table or ref.. or what else? [message #193574 is a reply to message #193555] Mon, 18 September 2006 05:47 Go to previous messageGo to next message
daad
Messages: 10
Registered: September 2006
Junior Member
i had in mind something like:
CREATE TYPE t_city AS OBJECT (
        name VARCHAR2(20),
        province VARCHAR2(20)
        );
CREATE TYPE t_area AS OBJECT (
        id NUMBER,
        name VARCHAR2(20),
        city REF t_city
        );
CREATE TYPE t_restaurant AS OBJECT (
        id NUMBER,
        name VARCHAR2(20),
        area REF t_area
        );
 
CREATE TABLE cities OF t_city (PRIMARY KEY (name, province) ENABLE);
CREATE TABLE areas OF t_area (PRIMARY KEY (id, city) ENABLE);
CREATE TABLE restaurants OF t_restaurant (PRIMARY KEY (id) ENABLE);

But i get an error when i try TO execute: CREATE TABLE areas OF t_area (PRIMARY KEY (id, city) ENABLE); The error says:

ORA-02329: a COLUMN of REF type cannot be UNIQUE OR a PRIMARY KEY
(something LIKE that, i just translated the error TO english)

How would you implement the advice you gave me on those tables?
I mean, how to add a sequence generated number to table, and you wanted to add it to which table? If you wanted to add it to Areas table, still how you can check that two areas with same id number are not linked to same city?
Re: Nested table or ref.. or what else? [message #193577 is a reply to message #193574] Mon, 18 September 2006 05:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I wouldn't.
I wouldn't touch object tables for a problem like this.

Assuming that you're passing the data through to a java front end that needs objects, I'd create a set of standard relational tables at the back end, and then either:
1) A set of procedures that load the requested data into object types and pass that back to the front end, (preferable, as it means that your Java guys don't hae to mess about writing SQL, and getting it badly wrong AGAIN) or
2) A set of views that cast the data as object types
Re: Nested table or ref.. or what else? [message #193583 is a reply to message #193577] Mon, 18 September 2006 06:22 Go to previous messageGo to next message
daad
Messages: 10
Registered: September 2006
Junior Member
but i have.. i have to use as much as possible the Object Relational functions of Oracle
Re: Nested table or ref.. or what else? [message #193585 is a reply to message #193583] Mon, 18 September 2006 06:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Really?
Why?

If you have to choose between an easy to maintain and performant solution that minimises the opportunity for problems to occur when interfacing with Java, but uses little of the Object Relational featureset, and one that is slower, harder to maintain and forces/allows the Java developers to write poorly performing queries at will, but uses more OR features, which would you pick?
Re: Nested table or ref.. or what else? [message #193594 is a reply to message #193585] Mon, 18 September 2006 07:04 Go to previous messageGo to next message
daad
Messages: 10
Registered: September 2006
Junior Member
just to understand, how to solve the situation in the slower harder way?
Re: Nested table or ref.. or what else? [message #193604 is a reply to message #193594] Mon, 18 September 2006 07:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Permit me to repeat my question.

Why the insistence on Object Relational features?
Re: Nested table or ref.. or what else? [message #193607 is a reply to message #193594] Mon, 18 September 2006 07:53 Go to previous messageGo to next message
daad
Messages: 10
Registered: September 2006
Junior Member
couln't i add a nested table "areas" into table City instead? in case, is that possible to specify a primary key on a nested table? if yes i would fixed the problem, since every nested table would be linked to one city only, so i would have to check on id of areas added only.
Re: Nested table or ref.. or what else? [message #193624 is a reply to message #193447] Mon, 18 September 2006 09:04 Go to previous messageGo to next message
daad
Messages: 10
Registered: September 2006
Junior Member
Or maybe better in this way?

CREATE TYPE t_city AS OBJECT (
        identifier NUMBER,
        name VARCHAR2(20),
        province VARCHAR2(20)
        );
CREATE TYPE t_area AS OBJECT (
        id NUMBER,
        name VARCHAR2(20),
        city NUMBER
        );
CREATE TYPE t_restaurant AS OBJECT (
        id NUMBER,
        name VARCHAR2(20),
        area REF t_area
        );
 
CREATE TABLE cities OF t_city (PRIMARY KEY (name, province) ENABLE);
CREATE TABLE areas OF t_area (PRIMARY KEY (id, city) ENABLE);
CREATE TABLE restaurants OF t_restaurant (PRIMARY KEY (id) ENABLE);

CREATE SEQUENCE city_sequence MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;


and using city_sequence when i have to add a city on CITIES. Then when i need to add an area i would retrieve the "identifier" attribute selecting the right row by name and province (that are primary keys) and setting it in "city" attribute of AREAS table.
Is it conceptually correct or there is any bug?
Re: Nested table or ref.. or what else? [message #193658 is a reply to message #193604] Mon, 18 September 2006 16:05 Go to previous message
daad
Messages: 10
Registered: September 2006
Junior Member
JRowbottom wrote on Mon, 18 September 2006 14:48

Permit me to repeat my question.

Why the insistence on Object Relational features?


Because i have to practice with those features, even if the case of study is not perfect to apply them. I'm just interested to do a correct system, not necessary efficent. thanks
Previous Topic: Subquery question.....
Next Topic: accessing a select column in where clause
Goto Forum:
  


Current Time: Wed Dec 07 06:35:17 CST 2016

Total time taken to generate the page: 0.28326 seconds