Re: how to add id to 2 tables

tonyb wrote:
> Hello,
> I have been tasked with creating a Ora 9.2 db and loading some data
> from a spreadsheet. Not quite sure how to do this. Well install db no
> problem.
> Currentlt the data looks like this:
> NodeName varchar2(255)
> Shelf int
> Slot int
> Port int
> ..
> The cuuning plan is to have two tables:
> NodeTable
> Id int
> NodeName

WHY? There's no value in making this table. The hint is that you are even having trouble thinking about how to populate it. Drop the pseudo-key Id and leave the nodename in the original table.

Then also check out some database text books, Look up the Normalization process. (Remember: It's the key, the whole key and NOTHING BUT the key, so help me Codd)

>
> CircuitTable
> Id int (?)
> NodeID int
> CircuitID varchar2(255)
> Shelf int
> Slot int
> Port int
> ..
> So for each of say 100-200 Nodes there will be say 1 to 100 Circuit
> details to fill in (curently held in a spreadsheet).

Seems like your spead sheet is the right first model. One table. Easy to load. easy to understand.

>
> The idea of the NodeTable, with generated Id, is to facilitate fast
> searching for a particular combination of Shelf/Slot/Port.

What? ORACLE can find rows based on those other columns just as fast. You don't make searches faster by replacing real values with pseudo keys. ORACLE is not a spreadsheet. If you know you will do frequent searches using those columns, then an index is what you need. But don't even bother thinking about that until you have loaded your data first.

>
> I am however unclear on how to populate these two tables.
> OK with SQL*Loader from comma separated vars (from spreadsheet) to the
> CircuitTable.

If you must load two tables from the same datafile, you just just use INTO TABLE twice. You haven't shown you really need both tables.

Another solution is load the file to a staging table, then with SQL or PL/SQL, insert the data into the final tables.
>
> But what is the easy way of generating the NodeTable complete with Id?
> a trigger on insert or PL/SQL after all the CircuitTbale has been
> populated or ?

A sequence and INSERT TRIGGER will work. But you really don't need that Id.
>
> Must I use an number (aka int) for the Id?

Again, this shows this Id attribute is not real. I don't think you need that separate table and thus no need for this Id.

>
> More than happy to read any relevant fine manual BTW...
>
>
> TIA
>
>
> Tony

Read about data normalization if nothing else. Understanding that will help you a lot.

  HTH,
  ed Received on Fri Sep 23 2005 - 14:55:19 CDT