22152

Multi-language Category Titles in Mysql Database

just looking for a bit of advice.

Summary for those who don't want to read it all: Is creating a table with just one single auto_increment column a reasonable idea?

Details:

I normally manage multiple languages in the database, with separate tables for the object's data and its descriptions in language. For example:

Table: MyObject (id auto_increment, height, width, depth. PK = id) Table: MyObjectDescription (id, language, description. PK = id, language)

The two id fields correspond, so I have data like:

MyObject +----+--------+-------+-------+ | id | height | width | depth | +----+--------+-------+-------+ | 1 | 10 | 10 | 20 | | 2 | 5 | 6 | 7 | +----+--------+-------+-------+ MyObjectDescription +----+----------+-----------------+ | id | language | description | +----+----------+-----------------+ | 1 | EN | First object | | 1 | IT | Primo Oggetto | | 2 | EN | Second object | | 2 | IT | Secondo Oggetto | +----+----------+-----------------+

My problem is that I have certain database objects which have <strong>no</strong> data <strong>apart from</strong> the description.

<strong>For example</strong>; a Category is defined simply as an id and a text description. I could use a single table like the MyObjectDescription table, but then I would have to generate the id myself, as I wouldn't be able to make use of the auto_increment column. This would also raise the possibility of race condition errors, or table locking problems. Or, I could use another table like MyObject which just has a single auto_increment column...

<strong>my doubt is:</strong> Is using a table with a single column just to simulate a sequence a reasonable idea? Or is it just plain stupid? Be aware also that my framework contains logic that automatically joins the object table to the description table.

Thanks for any input/ideas.

Gareth

Answer1:

Use MyObject (id).

Unless you have these objects in isolation from the rest of the database, you will need the PK comprised from id <strong>alone</strong>, so it can be referenced by FOREIGN KEYs.

Even in isolation, the enforcement of key would justify the separate table. Without it, you'd have to somehow generate a new id while preventing concurrent transactions from generating the same id for a different object. Obvious way to do it would be to lock the whole table (and select MAX+1), but that destroys scalability. It's better to just have a separate table, IMO. If you cluster it under InnoDB, that'll be just a single B-Tree anyway.

Recommend

  • Cannot use Turkish characters with Entity Framework
  • MySQL performance using AUTO_INCREMENT on a PRIMARY KEY
  • .NET RIA Services and Custom Data Model - CRUD Capabilities
  • JPS useBean with HashMap
  • OpenCV OpenNI calibrate kinect
  • How to Configure Log4Net Custom Object Renderer for Generic Objects?
  • Timeout on a php Peg Puzzle solver
  • update record in database using jdatabase
  • jinja2 template not found and internal server error
  • Find JSON nested nodes using multiple string values
  • chrome video src change not working
  • User messaging system
  • WPF Template Binding in ToggleButton UserControl
  • blade.php method outputting it's result to the form
  • Floated image with variable width and heading with background image
  • Can't delete or rename original file after resizing
  • D3 get axis values on zoom event
  • How can the INSERT … ON CONFLICT (id) DO UPDATE… syntax be used with a sequence ID?
  • Get history of file changes from TFS to implement custom “blame”-behaviour of exceptions
  • QLPreviewController hide print button in ios6
  • OpenGL 3.3 on Mac OSX El Capitan with LWJGL
  • How can I send an e-mail from a vbs script
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Uncaught Error: Could not find module `ember-load-initializers`
  • Can Jackson SerializationFeature be overridden per field or class?
  • How to set/get protobuf's extension field in Go?
  • Shallow update not allowed (git > 1.9)
  • How to show dropdown in excel using jrxml (jasper api)?
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Comma separated Values
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • NSLayoutConstraint that would pin a view to the bottom edge of a superview
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • Trying to get generic when generic is not available
  • how does django model after text[] in postgresql [duplicate]
  • How to stop GridView from loading again when I press back button?
  • python draw pie shapes with colour filled