2

I'm designing a database (for use in mysql) that permits new user-defined attributes to an entity called nodes.

To accomplish this I have created 2 other tables. One customvars table that holds all custom attributes and a *nodes_customvars* that define the relationship between nodes and customvars creating a 1..n and n..1 relationship.

Here is he link to the drawed model: Sketched database model

So far so good... But I'm not able to properly handle INSERTs and UPDATEs using separate IDs for each table.

For example, if I have a custom attribute called color in the *nodes_customvars* table inserted for a specific node, if I try to "INSERT ... ON DUPLICATE KEY UPDATE" either it will always insert or always update.

I've thinked on remove the "ID" field from the *nodes_customvars* tables and make it a composite key using nodes id and customvars id, but I'm not sure if this is the best solution...

I've read this article, and the comments, as well: http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

What is the best solution to this?

EDIT:

Complementing: I don't know the *nodes_customvars* id, only nodes id and customvars id. Analysing the *nodes_customvars* table:

1- If I make nodes id and/or customvars id UNIQUE in this table, using "INSERT ... ON DUPLICATE KEY UPDATE" will always UPDATE. Since that multiple nodes can share the same customvar, this is wrong;

2- If I don't make any UNIQUE key, "INSERT ... ON DUPLICATE KEY UPDATE" will always INSERT, since that no UNIQUE key is already found in the statement...

Livio Zanol
  • 45
  • 1
  • 5
  • nodes_customvars describes the many-to-many relationship between nodes and customvars. So technically the PK for nodes_customvars should be a composite key – SyntaxGoonoo Jun 24 '13 at 02:09

2 Answers2

0

You current entity design breaks 1NF. This means that your schema can erroneously store duplicate data.

nodes_customvars describes the many-to-many relationship between nodes and customvars. This type of table is sometimes referred to as an auxiliary table, because its contents are purely derived from base tables (in this case nodes and customvars).

The PK for an auxiliary table describing a many-to-many relationship should be a composite key in order to prevent duplication. Basically 1NF.

Any PK on a table is inherently UNIQUE. regardless of whether it is a single, or composite key. So in some ways your question doesn't make sense, because you are talking about turning the UNIQUE constraint on/off on id for nodes and customvars . Which you can't do if your id is actually a PK.

So what are you actually trying to achieve here???

SyntaxGoonoo
  • 900
  • 8
  • 10
  • Ok... I didn't knew that... The nodes_customvars table was created just to respect the NFs, since nodes x customvars was a many-to-many relationship. But I didn't knew that I had to use composite key instead of a new PK... I'm trying to create user-defined attributes for nodes... I don't know what is the best practice to achieve this... Other Links: - http://stackoverflow.com/questions/5106335/how-to-design-a-database-for-user-defined-fields; http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question – Livio Zanol Jun 24 '13 at 10:15
0

You have two options for solving your specific problem of the "INSERT...ON DUPLICATE KEY" either always inserting or updating as you describe.

  1. Change the primary to be a composite key using nodeId and customvarId (as suggested by SyntaxGoonoo and in your question as a possible option).

  2. Add a composite unique index using nodeId and customvarId.

    CREATE UNIQUE INDEX IX_NODES_CUSTOMVARS ON NODES_CUSTOMVARS(nodeId, customvarId);

Both of the options would allow for the "INSERT...ON DUPLICATE KEY" functionality to work as you require (INSERT if a unique combination of nodeId and customvarId doesn't exist; update if it does).

As for the question about whether to have a composite primary key or a separate primary key column with an additional unique index, there are many things to consider in the design. There's the 1NF considerations and the physical characteristics of the database platform you're on and the preference of the ORM you happen to be using (if any). Given how InnoDB secondary indexes work (see last paragraph at: http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html), I would suggest that you keep the design as you currently have it and add in the additional unique index.

HTH,

-Dipin

Dipin
  • 1,174
  • 9
  • 8