Hi,

I currently need to design a DB to cater for a dynamic object. I will describe the situation as best as I can.

I have an application which currently takes in some XML data. The xml data is permitted to run through a filtering process where the business people are allowed to choose what data they want.

This data is then stored inside an object and the data is to be inserted into 1 or many tables.

The idea is that the xml data can change dramatically each time but the expectation is to have the tables generic enough to cater for this.

I have thought about using dynamic tables but there would be a performance issue. What I would like to know is that what is the recommendations for these types of issues if there any.

Thanks

Chris

Interestingly: SQL Server 2005 supports XML as a datatype.
Never used it, but it may well be worth a look in!

we would still like to query the database for field type data. Besides we are working with ms sql. Is there any other alternative?

Thanks

Chris

SQL server IS a database server, published by Microsoft, sometimes erroniously referred to as 'ms sql.'

I would recommend that you avoid trying to implement a "dynamic table." Instead, in the selection process, when the business people choose the data they want, that you create a new xml record which contains the subset of data selected by the business users, and store that xml data.

Hi.
3...
...2...
...1...
BOOM!!!

What you are describing is not a database. I would call it a "data pile". It is going to explode in your face and fling manure across everyone who comes into contact with it.
Rule of thumb: don't design any database for clients that can't define their requirements.

hi thanks for the reply. I meant to say mysql not ms sql.

thanks for the reply blindman...

so what you are trying to tell me is that we should have is a fix field table for each new XML that comes in.

No. Blindman is saying that your users are requesting a coffeepot that doubles as a walk-in freezer. In short, if the users don't know what data is coming in, how can that data relate to anything? Is one user bringing in stock prices for the day, while the next door user is bringing in lottery numbers for the past week?

Hi,

thanks for the quick reply. What will happen is that the user will be presented with a full xml, the user will then select all those fields that they want and its the filtered xml which will be persisted and a schema of this new xml is saved. When other xmls come in the user will be offered a choice to choose from an existing plan or creating a new one. Does that sound more reasonable?

Thanks

Chris

No, that description looks like they want something that is both a coffee pot and a walk in freezer now, with the ability to become a space shuttle once they get a larger XML file. If your target machine was a HAL-9000, then this goal might be reasonable, but there are some potentially unpleasant side-effects to that too.

What happens when the XML file contains audio content, or better still video? How will your code adapt to handle that? What if some of the incoming XML contains data governed by HIPPA or SOX and the lawyers come looking to you for failing to provide the appropriate safeguards (and with a client like you've described this is only a matter of time, the question needs to be when will it happen, not will it happen)?

This project is an instance of Pandora's box. My advice is to run, and if you can't run then re-negotiate now!

-PatP

i would like to thank everyone for their advice and appreciate all the comments. I will be going back to argue now

Chris

Yes, Lasher.

The problem is that the sum total of your business requirements appears to be "Build us a database application that will hold any data and do anything we need it to."
Obviously, we can't help you with this. Because if by some chance you were to succeed you would put all of us out of business!
The other thing to keep in mind is that your users seem to be asking for an application unencumbered by any rules or constraints. Sounds great, right? A database that any user can do anything with? Problem is, one of the little understood benefits of a relations database is that they DON'T allow users to do anything they want. A well designed database demands that data follow rules and constraints that ensure that the data that eventually comes back out of it is reliable, consistent, and accurate.

might not be such a bad thing. kind of miss working for a living.

If the Conceptual Model is done correctly, before designing the Logical and Physical Model, you will be able to capture any new data without dedesigning the DB.

A conceptual model should start with a broad generic Ontology which can generically capture all db entities and relationships.

For example instead of having companies, customers, players, groups, employees, etc. you could store/classify all these entities as Parties.

The relationships between these entities (i.e. what u are usually familiar with as Foreign Keys and such) are also manage separately in entities (Link Entities).

So, when new relationships arise in the data you can dynamically store them by defining a new Association Type (i.e. A person may be a member of a group, an owner, an employee, a customer - all of which can be stored in existing tables (Parties and Party-Associations) as needed).

If you want to read more about these types of approaches there is a lot out there. Check into Events-based models, Object-Relational, Associative Models, etc.

Also, there are many standard Ontologies (check out DublinCore) which are being used in Metadata models in many implementations around the world These will help u design the Base Entities and get going.

Alternatively, you can always looks at XML as a flexible storage methods (although this is not my prefered way because it too is a type of data silo - requiring an intial DB Design).

Always start with the Conceptual before moving to E-R

Cheers

rdalter, i stumbled on dedesigning, figuring it was just a typo

but when i got to broad generic Ontology, i knew something was wrong with me

i've been designing databases professionally for about thirty years and i've never needed to use one of them things, whatever it is

also, something that puzzles me is why you can use such nice big fifty-cent words and then toss in the occasional "u" for what, populist appeal?

i think the world of database is slowly passing me by, because i've never even heard of data silos -- are they kept on server farms?

one thing i have learned, though, is never to over-generalize a design -- that's a slippery slope to EAV, and that way lies madness, as blindman would say