MySQL question (table design)
Code:
mysql> describe tags; 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? |
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) |
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 |
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 |
Hi Chris,
Quote:
The following is an example Code:
tag | value_add | my comment |
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 ;) |
Quote:
Quote:
Quote:
|
How about changing the enum to ('n','y') and make the filed not null?
|
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 |
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. |
Yah, see my post (#4)
:) |
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. |
Different approach
OK, it took a while before I had time to post, but I came up with the following solution:
Code:
+-------------------+---------------------+------+-----+---------+----------------+ 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 ..... |
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. |
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 |
All times are GMT -5. The time now is 05:58 PM. |