CODEWITHSUNDEEP
mysqlxmldatabase-designmetadata
neomech
neomech

Reputation: 303

Storing custom MySQL MetaData - best practices

I have a fairly large database containing a number of different tables representing different product types (eg. cars; baby strollers).

I'm using a website built with PHP to access the data and display it, and I allow users to filter the data (typical online product database sort of stuff).

I'm not sure if I went about storing my metadata the correct way. I'm using XML to do a lot of stuff, which requires making a product type table in MySQL first, and then adding information about each of the columns in that table in my big XML "column attribute" file. So I'll have the name of each column listed in the XML table with information about the column. I store localized names for the column in the XML file, and indicate what type of information about the product is being stored in the column (e.g. Is a column showing a dimension (to be listed in the product dimensions area) or a feature (for the features area)).

First off, am I way off base storing all this custom metadata in XML?

Secondly, if I should be storing some of it in MySQL (and I think I should be moving some of it there), what's the best way to do that? I see that I can make column "comments" in MySQL....are those standard fare for databases? If I move to Oracle some day, would I lose all my comment info? I'm not thinking of moving much information to the database, and some of it could be accomplished by just adding a little identifier to my column names (e.g. number_of_wheels becomes number_of_wheels_quantity, length becomes length_dimension)

Any advice from the database design gurus out there would be vastly appreciated. Thanks :)

Upvotes: 1

Views: 1642

Answers (2)

invertedSpear
invertedSpear

Reputation: 11054

First off, am I way off base storing all this custom metadata in XML?

Yes, XML is a great markup for transporting data in a nearly human readable format, but a horrible one for storing it. It's very costly to search through XML, and I don't know of a (good) way to have a query search through XML stored in a field in the DB. You are probably better off with a table that stores these things directly, you can easily convert them into XML if you need to, after you query them from the DB. I think in your case a table with the following columns would be useful: "ColumnName","MetaData" Would be all you need, populate with values as per your example:

__________________________________________________________________________________________________
|colDimension    | Is a column showing a dimension (to be listed in the product dimensions area) |
|colFeature      | a feature (for the features area)                                             |
--------------------------------------------------------------------------------------------------

This scheme will resolve your comments conundrum as well, as you can add another field to the above table to store the comments in, which will make them much more accessible to your middle tier (php in your case) if you ever want to display those comments.

I had to make a few assumptions as to intent and existing data and whatnot, so if I'm wrong about anything, let me know why it doesn't work for you and I'll respond with some corrections or other pointers.

Upvotes: 2

Imdad
Imdad

Reputation: 6032

See, your purpose is to keep the Meta data at a place. right? I'll suggest you to use the freely available tool Mysql Workbench. In this tool you have option to create ER diagram (or EER diagram). You can keep the whole structure and at any point of time you can sync with server and restore the structure. You can backup those structures also. Its kind of you have to learn first if you are not already using it. But at last its a very helpful tool for keeping the structure in an organized way.

Upvotes: 0

Related Questions