How to model data from arbitrary CSV’s in a database

Unfortunately there isn't a cookie-cutter way to do this. You have to start with some base records, write queries that join against them and then repeat. As you go deeper the queries get more complex as they have to go right back to the root.

If you have the foreign keys then you can automate the generation of the queries, as the joins will just be through the foreign key columns. It's not as hard as it sounds, but here are a few pointers if you take this approach:

• The queries are only going to retrieve the contents of the table at their leaf, so they are fairly straightforward to generate.

• Get the data out in a form that will load through bcp. As a bonus, you can generate bcp control files from the system metadata.

• You can probably quite safely just copy all of the reference data, which probably accounts for the majority of your tables anyway.

• You may have to start from multiple roots, and you may have to manually add join metadata if you have missing foreign keys. If your relationships to your leaf tables are not purely hierarchical then you may have to manually edit the metadata for the generator. In practice this means you probably want to start from the system data dictionary and then make up a configuration file for your generator that works around this.

• You may also need to add custom queries in for certain data items if the selection criteria involve more than one path to the record.

• Generate or write a script that uses bcp to load the tables to the destination in the right order.

Automation will probably get you 95% of the way with some manual intervention and the scripts aren't all that hard to write. It's not a 5 minute job, but it's certainly possible.

You could programatically generate SSIS packages to do the load, but I think that generating bcp control files is probably easier. Another option would be to just structure the scripts so they copy the data from the queries into another shadow database. This can then be backed-up/restored to wherever you want.

It's not a trivial undertaking but it's certainly not beyond the wit of man. As a bonus, if you can configure your script so the starting keys can be parameterised, then you can make a generalised utility to copy subsets of your application database. This will be quite useful for rolling out test environments.

This sounds like a really simply one-to-many relationship.

For SQL Server, I would write this like:

CREATE TABLE Devices
(
    DeviceID INT
    , DeviceName nvarchar(255)
);

CREATE TABLE Cards
(
    CardID INT
    , CardName nvarchar(255)
    , DeviceID INT
);

CREATE TABLE Ports
(
    PortID INT
    , PortName nvarchar(255)
    , CardID INT
);


INSERT INTO Devices VALUES (1, 'Test Device 1');
INSERT INTO Devices VALUES (2, 'Test Device 2');
INSERT INTO Cards VALUES (1, 'Card 1 in Test Device 1', 1);
INSERT INTO Cards VALUES (2, 'Card 2 in Test Device 1', 1);
INSERT INTO Cards VALUES (3, 'Card 1 in Test Device 2', 2);
INSERT INTO Cards VALUES (4, 'Card 2 in Test Device 2', 2);
INSERT INTO Ports VALUES (1, 'Port in Card 1, Device 1', 1);
INSERT INTO Ports VALUES (2, 'Port in Card 2, Device 2', 4);

SELECT *
FROM Devices;

SELECT *
FROM Cards;

SELECT *
FROM Ports;

This allows a Device to have multiple Cards, which in turn can have multiple Ports.

The results:

The 3 tables can be JOINed together like this:

SELECT DeviceName, CardName, PortName
FROM Devices 
    INNER JOIN Cards ON Devices.DeviceID = Cards.DeviceID
    INNER JOIN Ports ON Cards.CardID = Ports.CardID
ORDER BY DeviceName, CardName, PortName;

If you use LEFT JOIN like this:

SELECT DeviceName, CardName, PortName
FROM Devices 
    LEFT JOIN Cards ON Devices.DeviceID = Cards.DeviceID
    LEFT JOIN Ports ON Cards.CardID = Ports.CardID
ORDER BY DeviceName, CardName, PortName;

you get these results:

This is an image showing the table relationships: