foreign key help

[mbeals]

I have a database for tracking assets.  Because different assets have different attributes that need to be logged, I can't have a single table that handles every asset. 

 

I need a way to reference these individual tables back to a common table so that I can perform joins. 

 

So in essence, I have a `devices` table:

 

CREATE TABLE `Devices` (

`deviceID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`type` INT NOT NULL ,

`location` INT NOT NULL

) ENGINE = InnoDB ;

 

and tables for types of devices:

 

CREATE TABLE `Routers` (

`deviceID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`Model` INT NOT NULL ,

`Serial #` INT NOT NULL,

`IP`  VARCHAR(20) NOT NULL

) ENGINE = InnoDB ;

 

CREATE TABLE `Servers` (

`deviceID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`Model` INT NOT NULL ,

`Serial #` INT NOT NULL,

`OS`  VARCHAR(20) NOT NULL

) ENGINE = InnoDB ;

 

I know I can apply a foreign key across deviceID, so that keys across the different device tables don't clash.

 

So here are my questions:

 

1.  How can I reference the appropriate table in the Devices table, so that I can query data from the child tables without knowing what they are?  IE:  Select  Devices.*, Devices.type.*  from `Devices` join Devices.type using deviceID.  I know that doesn't work, but I hope it gets the point across.

 

2.  When I insert to this database, how do I make the auto increment key span the tables.  Do I just need to drop the auto_increment from the Devices table and use something like mysql_insert_id() to grab the auto-inc value from the subtable insert, then update the Devices table?  I would love to be able to write a single insert statement that would update Devices and the sub table at the same time as if it were a single entity.  The problem with this, however, is that I may need to be able to reference a simple device that doesn't have any attributes outside of what are already in the Devices table.

 

3. Can any of this be translated over to MSSQL?  Unfortunately I'm developing on mysql, because that is what I have to work with, but the company wants to eventually migrate it all onto their MSSQL server.  So far everything I've done translates, but I want to make sure the two servers handle foreign keys and the like similarly.

 

Any help would be greatly appreciated.

[fenway]

1. You can't have this magic work on its own.

 

2. Ditto.

 

3. Unknown.

 

The question really is why have the devices table at all....

[mbeals]

What I'm trying to get to is a way to reference multiple devices with different attributes from a single place.  I maintain a transaction log which logs activity, such as shipping a device out, or performing maintenance.  When I query this log, I would like to be able to retrieve the information about the device referenced in the same query.

 

So if I have table:

 

CREATE TABLE `TransactionLog` (

`transactionID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`deviceID` INT NOT NULL,

`timestamp` INT NOT NULL ,

`comments` FULLTEXT,

..........

) ENGINE = InnoDB ;

 

I need that deviceID to reference a unique device in such a way that I can write a join statement that would result in something like:

 

transactionID 

deviceID         

timestamp     

type               

OS                 

IP                   

comments

1

1

March 3, 2008

Server

BSD

192.168.1.5

Server Installed

2

2

March 5, 2008

Router

[/td]

192.168.1.1

Configuration updated

2

3

March 6, 2008

Switch-unmanaged

[td]

Eaten by wild boars

 

am I going to have to store the table name in the 'device_type' table, then write some crazy query using subqueries?

[fenway]

The problem is that you need to extract a field value to determine a table name, which is impossible to do using standard SQL.

 

Depending on what you're doing, it may be "easier" to have a transcaction log for each tyep of device, and then UNION them all.