LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 06-05-2008, 01:11 AM   #1
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Rep: Reputation: 282Reputation: 282Reputation: 282
MySQL question (table design)


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?
 
Old 06-05-2008, 05:52 PM   #2
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,261

Rep: Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028
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)
 
Old 06-06-2008, 07:15 AM   #3
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
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
 
Old 06-08-2008, 11:58 PM   #4
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,261

Rep: Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028
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
 
Old 06-10-2008, 12:52 AM   #5
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
Hi Chris,

Quote:
Originally Posted by chrism01 View Post
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).
 
Old 06-10-2008, 05:50 PM   #6
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,261

Rep: Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028
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
 
Old 06-11-2008, 12:32 AM   #7
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
Quote:
Originally Posted by chrism01 View Post
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 View Post
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 View Post
Its your system though ....
I know, but it must be bullet proof. If MySQL in future decides to change that, I have a problem.
 
Old 06-11-2008, 10:30 PM   #8
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
How about changing the enum to ('n','y') and make the filed not null?
 
Old 06-12-2008, 12:36 AM   #9
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
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
 
Old 06-12-2008, 02:45 AM   #10
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
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.
 
Old 06-12-2008, 06:33 PM   #11
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,261

Rep: Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028
Yah, see my post (#4)
 
Old 06-12-2008, 11:11 PM   #12
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
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.
 
Old 06-20-2008, 12:04 AM   #13
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
Different approach

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 .....
 
Old 06-20-2008, 12:16 AM   #14
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,261

Rep: Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028Reputation: 2028
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.
 
Old 06-20-2008, 03:30 AM   #15
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Slackware 10.1/10.2/12, Ubuntu 12.04, Crunchbang Statler
Posts: 3,786

Original Poster
Rep: Reputation: 282Reputation: 282Reputation: 282
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
 
  


Reply

Tags
design, mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with MySQL - how to design table? socceroos Programming 10 02-19-2007 10:29 PM
MySQL Table Replicating question... dolvmin Programming 3 02-21-2006 01:35 AM
MySQL Table Replicating question... dolvmin Linux - Software 1 02-20-2006 11:28 AM
PHP/MySQL table question newuser455 Programming 10 11-03-2005 04:50 AM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 11:30 AM


All times are GMT -5. The time now is 03:18 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration