Dynamic Database Design

Nova_C

I totally didn't intent the title to be alliterative, but whatever.

Anyway, I'm trying to design a database for asset tracking. It's a redesign of existing software and the major upgrade that really sparked this redesign was making user defined fields more flexible.

The purpose of this database is, of course, to track assets. Every asset must have a Location and an Asset Type, as well as either an Asset Number (For unique assets, like cars - has a serial number) or a quantity (For non-unique assets, such as paperclips). The relevant part of this is specifically the Asset Type. Every Asset must have an Asset Type. In this Asset Type is the list of fields that belong to an Asset.

Basically:

Asset Number | Asset Type | Location
   1234       |    Car      | Garage

However, these are only the default fields. Each Asset Type will have it's own list of fields. That way, in addition to the default fields, a Car Asset Type can also have the fields Engine Type, Number of Doors, Convertible:

Asset Number | Asset Type | Location | Engine Type | Number of Doors | Convertible
   1234       |    Car      | Garage    |   4 cyl      |       2          |      N

My problem is this: how do I go about creating the actual Asset tables in a SQL database? Here's my options:

Each Asset Type has it's own table. That way it'll have the correct fields. The problem? This will be a nightmare to search. Not only will it be programmatically complex to look up all the asset types to ensure a search covers all assets, but it will be expensive since every search will have to query the asset type list first to get a list of asset types, then query each and every table. This is my primary option right now.

Create two tables for assets, one for serial asset, one for non-serial with only the default fields. Then create two sets of tables for every data type (Such as strings, integers, booleans) such as:

Asset Number | Data

and

Asset Type | Location | Data

Basically, searches will be limited to the two main asset tables, then when a result is returned, all the fields can be looked up at that time. I don't like this because then field data is dependent on the order in which the fields are found, and it creates a metric fuckton of redundant data.

As you can probably tell I'm not very well versed in relational database design and I KNOW there has to be a more elegant solution.

MegaMan Addict

Ok, if I am understanding your problem correctly, you want to create a relational database that models company assets. Each asset has a type and each type has a list of attributes. So what I think you need is an attributes table.

AttributeID | AttributeName   | AttributeType
---------------------------------------------
1           | Location        | String
2           | Engine Type     | String
3           | Number of Doors | Integer
4           | Convertible     | Boolean

Now you can link your attributes to your asset with a linking table:

AssetType | AttributeID
-----------------------
Car       | 1
Car       | 2
Car       | 3
Car       | 4

So your asset table now looks like:

AssetNumber | AssetType | AttributeID | AttributeValue
------------------------------------------------------
1234        | Car       | 1           | Garage
1234        | Car       | 2           | 4 cyl
1234        | Car       | 3           | 2
1234        | Car       | 4           | False

Let me know what you think. Also, like all things in the realm of programming, there are as many different ways to solve a problem as there are programmers so this is by no means the only solution or even the best one, but it is the one that sprang to mind for me.

Lewisham

This is the way I would think about doing it; but you don't need the AssetType in the Asset Table. As each AttributeID is tied to an AssetType in the linking table, you could have a linking table ID, and put that in the Asset Table. That way, you're not replicating "Car" down the table for different attributes.

I would expect neither of these solutions are perfect, or might work 100% in your case, but they're a good jumping off point for thinking about it. Definitely an attributes table which is applied is the way to go.

Nova_C

Ahh, that's not bad. Hmmm....

I'll get back to you, but great suggestion.

Nova_C

Okay, here's what I'm thinking:

I need two basic setups because unique and non-unique assets can't be handled in the same way. Primary keys also ensure there is no duplication in the database, which is very, very bad. So I go with my second idea, using an attribute table to eliminate my concerns.

Two tables:

Asset Number | Asset Type | Location

and

Asset Type | Location | Quantity

Asset Number is the primary key for the first table and the Asset Type + Location are joined as the primary key for the second table (Unique assets will never share an asset number with another asset and non-unique assets that are the same type in the same location will always have their quantities combined).

For the user defined fields that go with that:

Use an attribute table like you guys suggested, but have additional tables for the assets:

ID | Asset Type | AttributeID | Data

The logic behind that table design is, for unique assets the Asset Number will go in ID and for non-unique assets, the location will go in the ID. The reason I'm leaving Asset Type in there is because it has to be there for non-unique assets since it's part of the primary key and if it's there I may as well use it for unique assets to streamline searching.

For the record, both locations and asset types use a serial ID, so the values in those fields will be numbers, not the text names.

Okay, so my question: Can a field have an undefined data type? That is, do I have to create a table for each data type, or can multiple data types go under the Data field in a single table?

If I can't do that, I may have to create two tables for each data type. Not a big deal, but the Asset Number is alphanumeric, so it has to allow strings whereas a location ID is an integer so the ID field may not work for both unique and non-unique assets.

hippofant

I am by no means an SQL expert, but no, fields have to have types. Not entirely sure what you're talking about regarding using a single field for both Asset Number and Location, since Location's in both tables so it'd be the same field for Asset Number/Quantity, which is a bad idea since since you can't tell the difference between an asset number and an asset's quantity.

You can definitely use one table, if size isn't an issue.

Asset ID (PKey) | Asset Number | Asset Type | Asset Quantity | Location

And then use a unique index on (Asset Number, Asset Type, Location) combined. Non-unique assets get a 0 for the Asset Number. Then one attribute table, FKey-ing on (Asset Number, Asset Type, Location) combined:

Attribute ID (PKey) | Asset Number | Asset Type | Location | Attribute Header | Attribute Desc

So you'll have a DB looking something like:

Asset ID (PKey) | Asset Number |  Asset Type | Asset Quantity | Location
              1 |            0 |  Paper Clip |           1000 | Storage
              2 |            0 |  Paper Clip |             50 | Front Desk
              3 |    955433612 | Photocopier |              1 | Front Desk


Attribute ID (PKey) | Asset Number |   Asset Type |    Location| Attribute Header | Attribute Desc
                  1 |            0 |   Paper Clip |    Storage |            Color |            Red
                  2 |            0 |   Paper Clip | Front Desk |            Color |            Red
                  3 |    955433612 | Photocopier | Front Desk |            Maker |          Xerox

You'll probably want the Asset Type and Location fields to link to Asset Type and Location tables, so you can't just enter nilly-willy asset types and locations. Maybe use constraints on the Asset Type, or just limitations to the front-end, so certain asset types, like photocopiers, are necessarily unique (given asset numbers). Same with attributes.

It ain't how they teach you to do it in a DB class, but it'll work if you want to use just one Asset table, which I imagine might be a business requirement. (Or be easier due to business requirements.) A tightly-coded front-end would be needed to make this db work (ie, one made by you).