0

I have a LAMP application that needs to accept external data sources from our users. Generally, these are just simple lists (e.g. so the user can have a customized drop down list). However, I am seeing the need for more complex data. For example, two customized drop downs wherein the second drop down is dependent on the selection in the first drop down.

Is there a way to store external relational data in my database in such a way I can query it also?

StackOverflowNewbie
  • 39,403
  • 111
  • 277
  • 441
  • Think I must be missing something, you really wanting to use external datasòurces or just import the data into your database with a simple parent/child table? – vickirk Aug 09 '11 at 13:31
  • I want to import the external datasource in my database in such a way that I can query it. Generally, it's just a simple list. I think I see some situations when it requires more than a simple list. – StackOverflowNewbie Aug 09 '11 at 13:36

2 Answers2

0

In a normalised database schema, you would model this with a self-reference in list, i.e.

create table list (
  id int,
  parent_id int,

  primary key list_pk (id),
  foreign key list_self (parent_id) references list (id)
)

If dropdown option number 5 has dependent options, you can query for them like this:

select * from list where parent_id = 5
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • the simple parent child example was a simplistic example. What if I needed to let the user import a more complex schema? – StackOverflowNewbie Aug 09 '11 at 13:38
  • You mean, like XML? I'm afraid MySQL is pretty poor with advanced data structures... But if this is just for some use cases, you could store XML files as `text` and manipulate that data entirely in your application? That depends on your use cases... – Lukas Eder Aug 09 '11 at 13:44
  • The input structure can be CSV, XML, JSON, or whatever. The key thing is that I save it in the database somehow and then somehow be able to query it. – StackOverflowNewbie Aug 09 '11 at 13:46
  • This looks like a good, similar question for you: http://stackoverflow.com/questions/5092030/most-efficient-method-for-persisting-complex-types-with-variable-schemas-in-sql – Lukas Eder Aug 09 '11 at 13:49
0

Not in MySQL, unless you define a structure for it, break up the data and store it in that structure. It would however be cumbersome. You can define a hierarchical structure in a single table by making a reference to a parent item in the same table. This way you could store even XML elements and their attributes in a single database, having only three tables for document, elements and attributes. Querying these tables would be very hard, especially in MySQL. In Oracle you got CONNECT BY which allows recursive queries, but it is slow.

Maybe you should look into No-SQL databases like Mongo, which is better designed for these kind of tasks.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • I'm not so familiar with No-SQL. Can you explain how that would resolve my problem? Also, my application is already in MySQL. Would I use No-SQL and MySQL side by side? – StackOverflowNewbie Aug 09 '11 at 13:40
  • Mongo allows you to store collections of objects that can have properties being objects themselves. You can perform queries on these collections. I'm not sure if it will solve your specific problem, and I'm not too experienced myself with it, but I thought I'd at least point out its existance to you, so you could see for yourself. It would be possible to use them side by side, although you will need to do some work if you want the same data to be available in both. – GolezTrol Aug 09 '11 at 13:44