Very Computer

4 tables

CREATE TABLE computers(
comp_id  INTEGER   IDENTITY,
name     VARCHAR(32)   NOT NULL,
more_fields  VARCHAR(100)
)

CREATE TABLE computer_aliases(
comp_id  INTEGER,
name     VARCHAR(32)   NOT NULL
)

CREATE TABLE op_systems(
os_id  INTEGER   IDENTITY,
comp_id  INTEGER    NOT NULL,
name     VARCHAR(32)   NOT NULL,
more_fields  VARCHAR(100)
)

CREATE TABLE op_system_aliases(
os_id  INTEGER,
name     VARCHAR(32)   NOT NULL
)

Ok, I'm writing a stored procedure to insert the aliases
into the different alias tables...

CREATE PROCEDURE up_AddNewCompAlias

)

Rule: name can be used only once for all 4 tables

a valid computer ID, then make sure the alias passed isn't
in use in either a computer, computer alias, os_system,
os_system_alias.
  Obviously looking for the most efficent way of doing
this.  My current choices I can see are:

1.  check for valid ID
    check name against computers
    check name against computer_aliases
    check name against os_systems
    check name against os_system_aliases
    add if all checks are okay
    if check fails error with message of already assigned
computer/os_system name

2.  check for valid ID
    check name against union of computers +
computer_aliases + os_systems + os_system_aliases
    add if checks are okay
    if check fails error with message of already assigned
computer/os_system name

3.  check for valid ID
    check name against union of computers +
                                  computer_aliases
    check name against union of os_systems +  
                                  os_system_aliases
    add if all checks are okay
    if check fails error with message of already assigned
computer/os_system name

  With #1 I'm doing a lot of selects of string columns for
a good alias.
  With #2 I'm doing a union of 4 tables (~17,000 total
rows)
  With #3 It's a mix of the first two

  Which is the most efficient way of handling this type of
searching?

Implement your unique alias rule as a constraint and then trap the error at
INSERT time.

Create an Aliases table:

CREATE TABLE Aliases (aliasname VARCHAR(32) PRIMARY KEY, aliastype CHAR(2)
NOT NULL CHECK (aliastype IN ('CO','OP')), UNIQUE (aliasname, aliastype))

and then reference the compound key from your other tables. This ensures
uniqueness across tables:

CREATE TABLE Computers (comp_id INTEGER PRIMARY KEY, aliasname VARCHAR(32)
NOT NULL, aliastype CHAR(2) NOT NULL CHECK (aliastype='CO'), FOREIGN KEY
(aliasname, aliastype) REFERENCES Aliases (aliasname, aliastype))

CREATE TABLE OP_systems (op_id INTEGER PRIMARY KEY, aliasname VARCHAR(32)
NOT NULL, aliastype CHAR(2) NOT NULL CHECK (aliastype='OP'), FOREIGN KEY
(aliasname, aliastype) REFERENCES Aliases (aliasname, aliastype))

A lot less hassle than all those UNIONs.

--
David Portas
------------
Please reply only to the newsgroup
--