[SOLVED] MySQL question (table design)

Wim Sturkenboom · 06-05-2008, 01:11 AM

Code:

mysql> describe tags;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| PK_tag     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| tag        | char(255)           | YES  | MUL | NULL    |                |
| date_valid | date                | YES  |     | NULL    |                |
| value_add  | enum('n')           | YES  |     | NULL    |                |
| FK_asset   | bigint(20) unsigned | YES  |     | 0       |                |
+------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Above is the description of a table in an asset tracking system that I'm currently designing.

The intention is that a tag can occur multiple times, but can only occur once with value_add set to 'n'.

I hope that that makes sense. My solution is to create a unique index
on the combination of tag and value_add.

The question is if that's the correct approach or that there is a better solution to solve it?

chrism01 · 06-05-2008, 05:52 PM

The prob is that NULLs are not indexed, so that won't work.
Prob need to know why you are doing this. (I've done a few asset mgr systems myself)

Wim Sturkenboom · 06-06-2008, 07:15 AM

Hi chrism01, thanks for the reply

Example:

You have a PC case. This is the 'parent' asset and has a physical asset tag that will be stored in the asset management system.
Next we have a motherboard, memory etc. They add value to the PC case. In my situation these are 'children' who don't have an physical asset tag but inherit (in the asset management system) the asset tag from the parent.
In that situation, PC case will be in the system with tag='xxx' and value_add='n'; nothing else can have that combination (as it's a unique index). All other components that are used to build the complete PC will have tag='xxx' and value_add=null.

You state that this will not work; however, it does give me what I need from a functionality perspective.
It might well be that I abuse indexes in this way (I don't know).

Any thoughts?

thanks WimS

chrism01 · 06-08-2008, 11:58 PM

Well, as you state, you've got parent/child, so create a child table with a parent id (foreign key) and each child object/row has its own id, prob just a unique auto-incremented id, with optional serial num. Most HW has a manufacturers serial num somewhere.
Parent table is the PC case.
Its not unknown for PCs to have 2 screens (eg developers) or more in some cases...
As I said, MYSQL (and I believe others) do not count 'NULL' values in indexes, so that won't enforce your reqd rule.
You could try to force uniqeness by having a child_asset_type NOT NULL col, in which case the index is:
UNIQUE INDEX idx1 (pc_tag, child_asset_type)
ensuring that each case has only one item of that type attached.
HTH

Wim Sturkenboom · 06-10-2008, 12:52 AM

Hi Chris,

Quote:

Originally Posted by chrism01

As I said, MYSQL (and I believe others) do not count 'NULL' values in indexes, so that won't enforce your reqd rule.

It does

Probably because, as you explained to me, null values are not indexed. So I can have multiple tags with a null value in the value_add column and the same tag number in the tag column (those are the children). I can however only have one value_add with the value 'n' for that same tag number in the tag column (the parent).

The following is an example

Code:

tag | value_add | my comment
----+-----------+-----------
1   |  'n'      | parent (and can only occur once)
1   |  null     | child
1   |  null     | child
2   |  'n'      | parent (and can only occur once)
3   |  'n'      | parent (and can only occur once)
3   |  null     | child
4   |  'n'      | parent (and can only occur once)

I did consider an auto-increment for the value_add column, but rejected the idea as it does not force the uniqueness as I see it (any combination of the tag and the value_add will be unique and therefore uniqueness is not enforced).

chrism01 · 06-10-2008, 05:50 PM

Ok, in your case that would work, but its not something I'd like to rely on.
(Its been a while since I used Oracle or sybase, but iirc, Oracle is able to index at least one NULL)
Really you should use a child table instead, its more RDBMS, less flat file design.
Its your system though ....

Edit:
Had to redo this comment as a sparkie came into the office last night and pulled the wrong breaker

Wim Sturkenboom · 06-11-2008, 12:32 AM

Quote:

Originally Posted by chrism01

Ok, in your case that would work, but its not something I'd like to rely on.
(Its been a while since I used Oracle or sybase, but iirc, Oracle is able to index at least one NULL)

Thanks, that's what I wanted to know.

Quote:

Originally Posted by chrism01

Really you should use a child table instead, its more RDBMS, less flat file design.

I'll have to figure out how that is going to work exactly.

Quote:

Originally Posted by chrism01

Its your system though ....

I know, but it must be bullet proof. If MySQL in future decides to change that, I have a problem.

graemef · 06-11-2008, 10:30 PM

How about changing the enum to ('n','y') and make the filed not null?

Wim Sturkenboom · 06-12-2008, 12:36 AM

Hi graemef,
that does not work ion this case. In that case you can only have one parent and one child with the same tag and after that you run out of unique indices that involve the specific tag.

WimS

graemef · 06-12-2008, 02:45 AM

Okay I see what you're saying. I think that you need a table for containers which will be the item that has the physical asset tag and then another table that holds the details of the extras.

container
=========
PK_tag
tag
date_valid
FK_asset

extra
=====
PK_tag
tag

with the extra table adding additional tag details to the container object.

chrism01 · 06-12-2008, 06:33 PM

Yah, see my post (#4)

Wim Sturkenboom · 06-12-2008, 11:11 PM

I did some research (looking for what SQL standards say about NULL and index) and came across wikipedia - Null (SQL)
Chris already stated that he would not rely on it and the above link indeed confirms that.

As said, I'll have to rethink the design.
There is another table that contains the actual assets (it was not mentioned earlier as it was not relevant for the question) and I think that I can do something with that.

Wim Sturkenboom · 06-20-2008, 12:04 AM

OK, it took a while before I had time to post, but I came up with the following solution:

Code:

+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| PK_asset          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| make              | char(255)           | YES  |     | NULL    |                |
| model             | char(255)           | YES  |     | NULL    |                |
| serial            | char(255)           | YES  |     | NULL    |                |
| description       | char(255)           | YES  |     | NULL    |                |
| FK_owner          | bigint(20) unsigned | YES  |     | NULL    |                |
| FK_bu             | bigint(20) unsigned | YES  |     | NULL    |                |
| location          | char(255)           | YES  |     | NULL    |                |
| FK_asset          | bigint(20) unsigned | YES  |     | NULL    |                |
+-------------------+---------------------+------+-----+---------+----------------+

Above the (slightly simplyfied) assets table. If the asset (let's say asset B) is an asset that adds value to another asset (lets say asset Z), FK_asset for asset B will be set to the value of the PK_asset of asset Z.

The tags table will no longer be used to identify the parent and the children. Assets where FK_asset is 0 are parent and those can be tagged (read: linked to the tags table).

A query to find an assets will be something like:

Code:

select parent.*, child.* from assets as parent left join assets as child .....

chrism01 · 06-20-2008, 12:16 AM

Given that FK_asset can be NULL, I'd use that (NULL) as the parent identifier, not zero. Or, make it NOT NULL and zero = parent.
The latter might be easier in terms of not having to worry about NULLS in WHERE clauses.

In my latest system, I've got a separate table for each asset type (about 25) because not all the columns ie attributes for a given asset eg a switch make sense for eg a screen.
You may want to think about that.

Wim Sturkenboom · 06-20-2008, 03:30 AM

Thanks for the feedback on the NULL.

With regards to your other suggestion, I currently don't have specific attributes for assets.

Enjoy the weekend, WimS