mysql - table design for embeding system

Question

I am working on an embedment section on my site where users can embed different media from various services, youtube, myspace music, vimeo etc

I am trying to work out the best way to store it. Users do not have to embed all of the options and can only embed one of each type (one video for example).

Initially I thought just have a table with a row per embeded item like so:

• embedid (auto increment primary key), userid, embedded_item_id (e.g a youtube id)

but then I realised that some embedable items require multiple arguments such as myspace music so I thought id make a table where each user has one row.

• userid, youtubeid, vimeoid, myspaceid1, myspaceid2

but it seems a bit clumsy especially considering there will always be empty rows as users can not ever have all of them. Does anyone have a better solution?

Answer 1

• `EmbededItem' table has columns common to all items.
• YouTube, Vimeo, MySpace have only columns specific to each one.
  
  

Answer 2

So, here's what I'd do in such a situation: Setup your table with columns for your primary key and userid fields and anything else you may need to identify the user or application (maybe a 'mediatype' field). The rest, put into a VARCHAR field, make it large enough to hold lots of data. Not sure how much space you would need, but I'm going to venture a guess that you will need between 1K and 4K+ of space.

The reason for a VARCHAR field: you never know what other new fields you will need in the future. Let's say next year youtube adds another parameter, or a new media format comes along. If you model your database to represent all fields individually, you will create an application that is not scalable to future or other media formats. Such modeling is great when you're describing a system on paper, but not so good when you implement code.

So, now that you have a varchar field to store all your data in, you have several options for how to store the data:

1. You can store the data as an XML document and parse it on input/output (But you will most likely need more than 4k of space), and you will incur the cost of parsing XML.

2. You can store the data as whatever data format you may need for your application (serialized object for java, JSON for javascript, etc). If you're serializing an object, you may also need more than 4k of space, and a VARBINARY field, not VARCHAR.

3. comma delimited string, although this fails if your strings contain commas. I probably would not recommend this.

4. null delimited key/value pair strings, with a double null at the end. You will need a VARBINARY data field for this one.

Number 4 is my favorite, and something I would recommend. I've used this pattern for an existing web project, where my strings are stored in format of:

'uid=userid/0var1=value1/0val2=value2/0url=urltosite/0/0'

Works like a charm. I use the data to build dynamic web pages for my users. (My application is C though, so it deals well with parsing a character array).

Your application could use the data from your first columns (like 'mediatype') to execute specific parsing routines if required, and use the VARCHAR/VARBINARY fields as input. Scaling to new types of embeddable media would be as simple as writing a new (or extending an existing) parser and defining a new 'mediatype' value.

Hope this helps.