What is wrong with the relational model

[Merrion]

Increasingly I am noticing that the majority of data in relational databases is what I would call "navigational attributes".

For example, there might be a table called [Transactions] and in it there are a bunch of properties that navigate to who the transaction is for - maybe ClientId, InvestorId, AccountId.
Which is fine, except the transactions for one client are never used in conjunction with transactions from another client.

What think ye - is the way we use the relational database model broken or have I gone bat-mad?

[techgnome]

huh? I'm not sure I followed. Are you saying that you have the same ClientID for all entries in the table? Just a basic gut feeling is that the table as presented is a lookup table that allows for a many to many relationship. A single investor can have multiple accounts, and a singe account could have multiple investors.

But it also sounds like there's probably more to it than that, and it's possible it's been over engineered... I've seen that happen. In fact I'm dealing with that right now. there's a part of the system that was over engineered... and then done badly... sigh...

I don't think the model is broken per se... it's usually the implementation that's broken.

-tg

[Merrion]

huh? I'm not sure I followed.

I don't think the model is broken per se... it's usually the implementation that's broken.

-tg

Yup - it is mostly me going insane here. What I'm thinking about are those tables that have the same type of thing but they are not interchangeable.
For example - a table of exchange rates has fields: [from currency code], [to currency code], [rate date], [rate]
So I'm storing [from currency code] and [to currency code] for every entry.
If instead I had a separate table for each currency pair and a lookup of "select the right table then get the data" (Navigate->Get) I'd be much better off.

And don't get me started on tables like [Settings] or [Lookup] :-(

[techgnome]

We support multicurrency by havign a "BaseCurrencyID" ... some where else we have exchange rates and stuff... allows us to do the calculations on the fly from what ever the base was to what ever it needs to be. Then we have a built-in function, give it a base currency, the target currency, a date, and i spits back the exchange rate for you.
In your case, I'd just store the id of the exchange rate... not sure why it would be necessary to store the from and to currency ids... to quote an annoying commerdcial "Now that's just crazy..." and it's not even crazy generous.

-tg

[szlamany]

You are letting the "data warehouse" model cloud (no pun intended) your "data transactional" world.

Cloudy- right?

Ne'er the twain shall meet...

[szlamany]

Makes me think "temporal"...

[FunkyDexter]

If instead I had a separate table for each currency pair

...you would have a massive number of tables and it would exponentially increase with the number of currencies you dealt with. N^2 I believe. So if your company dealt with just 2 currencies you might be fine because that's just four tables but if they dealy with 10 that would be 10 tables. I did a quick google and there are apparently 181 official currencies circulating which would leave you needing 32,761 tables. Thirty two thousand rows is reasonably easy to deal with, thirty two thousand tables is not.

If you're really set on a model like you describe then I suggest you look at some of the NoSQL platforms that are out there. I've been playing around with MongoDB alot lately and it has alot of merit as an alternative aproach. It's not perfect and comes with all its own baggage but it does handle some things better than a relational model. Basically each exchange rate (or currency pair) would be a document. A document is roughly analogous to a recofrd but each is indidividual and doesn't have to have the same attributes as the other documents of that type - so it's kind of a bit like a table too.

There are problems with the relational model that are worth considering. For me the big one is that it's structurally inflexible once created. Imagine I have a CRM system which has a custoemr table. I put fields in that table for home and work telephone number. I try and sell my system to customer who says "wait, we also store mobile numbers", and then a second customer who says "we do fax numbers". Then a third customer says "we do international numbers so they need to be longer than you designed that fied to be". In each case I cannot sell it to the customer without engaging in some re-design. I certainly cannot sell it straight off the shelf.

What I really want to do is let the custoemr add their own fields directly but that's extremely difficult to do in a relational model. I can chuck in a few undefined "User Fields" but that means I've got to limit them to an arbitrary number, they'll probably have to be text fields and I can't apply any validation rules to them. I can try and implement an Entity Value Pair pattern... don't do this... just don't... if you don't know why just google for it... I can tell you from about three years of bitter, personal experience that all the reasons you'll find are very valid and insurmountable. Or I can actually let the customer directly affect the deisgn of the tables... and have an unusable system in very short order. It's a problem the relational model simply can't solve. It was this that first prompted me to go looking at NoSQL databases which handle this sort of thing much better. Mind you, the problems enforcing relational integrity in NoSQL database have now got me looking back at SQLServer again and giving serious consideration to it's XML capabilities. I'm not sure that's perfect either and I increasingly think the truth is that I'm really hunting for a least-worst solution and whatever I finally settle on is going to involve a certain amount of pain.

[Merrion]

It seems to me that Azure table storage follows that "NoSQL" model in that other than the partition key, row key and timestamp you can have different properties in different rows in the table... there is no enforced table schema.

In my case the currency rate table gets very large very quickly as it is generating off a live ticker. Average one record per currency pair per second - some much higher, some much lower (some never) .

[FunkyDexter]

Average one record per currency pair per second

Really?! Are you storing it when the rate changes or just every second. If it's the latter tht seems crazy and I'd only store the changes - everything else is redundant. I struggle to believe that exchange rates change every second but it's not something I know alot about.

[szlamany]

...the transactions for one client are never used in conjunction with transactions from another client.

What think ye - is the way we use the relational database model broken or have I gone bat-mad?

I might gave come off flip at first - but in reality isn't this the realm of OLAP??

But what you have is OLTP - all about getting those transactions under the correct client - yadda, yadda.

Analyzing the transactions of one client vs. the other - isn't that OLAP? Which naturally forces you out of 3rd normal form - the data gets "pre-joined" in flatter structures...

Actually I don't subscribe much to this anyway. I witnessed many failed attempts at gathering transaction data for analysis purposes - ad-hoc being the requirement - simply fail.

Azure looks different - but it's still too parent-child hierarchal to really work.

My best efforts in this area - that have already paid off - all do the heavy lifting in "smart" UI pages - allowing the user to work with the data themselves to build and run comparisons.

If instead I had a separate table for each currency pair and a lookup of "select the right table then get the data" (Navigate->Get) I'd be much better off.

That "after the fact" type of JOIN - "select the right table" - that just begs to be done in the "after-data" layers.

Your relational model has failed you in this regard.

I've always put 100% of my business logic in SPROCS - because I wanted that ability to "massage" the data "closer" to the data before sending it out. Certainly not traditional - and I've had to defend it many times - but it works for me.

I remember reading some book decades ago now - where it discussed the normal forms - and just 1st, 2nd and 3rd made sense to "try to achieve" in the database - all other forms - 5 and so on- all apply to how you drop that data into memory and work it from there.

[Merrion]

Really?! Are you storing it when the rate changes or just every second. If it's the latter tht seems crazy and I'd only store the changes - everything else is redundant. I struggle to believe that exchange rates change every second but it's not something I know alot about.

Only storing changes - but storing them as [from currency][to currency][timestamp][price]. I think that just storing a tape of [timestamp][price] for each traded currency will make a difference, but will need to check the impact.

[FunkyDexter]

Wow, they change alot more frequently than I'd have expected then. I guess the question would become, do you need that history of every single change? If so then I think you're kinda stuck with it. If you need the data you need tha data.

BTW how many currencies are there? And therefore, how many exchange rates (N^2)? And how long do you need to maintain the history for? I'm curious because you may be panicking prematurely about the number of rows you'll end up with. A powerful set up will cope with billions of rows and while an exponential function gets big quick as N increases, it also gets small quick as N decreases.

[Merrion]

Yes - I almost certainly am over-thinking it. I actually only have 5 - 20 currency pairs traded by any given client... so I'll probably get away with it.

[szlamany]

Hey - I thought this was in chit-chat for a reason - you guys are getting all serious here...

[FunkyDexter]

you guys are getting all serious here

Sorry, wholly uncharacteristic of me and I apologise for letting the side down.


POST RACE!!!

oops, wrong thread.

[szlamany]

I was gonna add "Back to POST RACE" to my last post also - that's sick

Actually I've been developing my own database system here for the past two years - and that link to Azure Merrion posted gave me some great ideas on how to describe how Azure is just a fail at trying to topple the RDBMS hurdles.

[Merrion]

I just drew out what I thought my architecture should look like - turns out its a file directory system :-)

[FunkyDexter]

I was gonna add "Back to POST RACE" to my last post also

I considered referencing your thread on controlling log growth but I thought I'd better not. Now that would have been sick.

I think the NoSQL aproach has legs but it's not a silver bullet. It's not applicable for every circumstance and one of the problems is that alot of the guys who've got an interest in it (like the guys behind Azure, for example) will try and tell you it is. Couple that with how attractive an unstructured aproach sounds and you're seeing it used as a basis for tons of applications for which it's patently unsuited.

All NoSQL DBMSs (or at least, all the ones I've looked at) are essentially document stores (not a paper document but rather a single, discrete block of information that doesn't strongly relate to any other block of information). Forums are a great example of this. A thread is a thread. The posts in it are part of the document that is the thread. They never relate to anything else so they don't need to be entities in their own right. And when you want to retrieve a thread you want the whole document (ie all the posts) as one atomic lump. If that's what the data you're storing looks like then a NoSQL database is ideal. But if you've got lots of data that relates to other entities in different ways, e.g. customers having orders that were sold by salesmen who report to sales managers etc. then your data is relational and it shouldn't come as a great surprise that a relational database is likely to serve you much better. I think most data that businesses use is relational in nature and that's why NoSQL databases have failed to gain the traction they originally promised.

It's that relationality that's prompting me to rethink my choice to move to NoSQL for my CRM and marketting app (it's a real thing that I may finish one day and make my fortune from). The NoSQL aproach certainly allowed me the flexibility I wanted but weakness in maintaining the relationships just kicked me in the teeth over and over again. That's why I'm looking very closely SQL Server's xml capabilities. I can map the relations into normal primary and foreign key columns giving me decent relational integrity etc. but move all the flexible stuff into an xml document on each record. It gives me the best of both worlds. My prototyping so far has been very sucessful but I do have serious concerns about how well it'll scale so that's what I need to hammer next. It's likely that a user would want to search by one of the fields that's in the xml and that's going to struggle to perform. I know SQL Server supports indexing the xml but from what I've read these indexes are absolute memory hogs so they may or may not be feasible. Alternatively I could bring search fields out of the xml and into the "row header" columns but that's going to require all sorts of extra synching.

Anyway, I got all serious again so I'm just going to point at you and say "hur, hur, hur, controlling your log growth".

turns out its a file directory system

The NoSQL might well be what you're looking for. Files are documents.

[szlamany]

Azure to me - after seeing the image in that link from Merrion - is just JSON. It's name-value pairs within a 4-tier dictated JSON/object memory space. Of course it works well in the cloud!

Azure is modeled from a memory structure developed in the programming-language-world's bastard-child JavaScript - that's actually laughable!

[Merrion]

There are 3 models in Azure storage - totally structured (SQL), partially structured (Tables) and unstructured (Blobs).

[szlamany]

So that's SQL, JSON and nothing - that is an interesting 3 choices

[Shaggy Hiker]

The third option should be called Zen. The data is. Only the mind gives it shape.

[dunfiddlin]

The data is.

Or is it?

[techgnome]

Depends. If it is quantum data it is both is and is not until it is observed, at which point it depends on in which state it was observed.

-tg

[Merrion]

Since "data" is plural it must exist

[dunfiddlin]

Since "data" is plural it must exist

Er .... that's what passes for the ontological argument these days, is it? My old philosophy teacher must be spinning in his grave (assuming he's actually dead, of course!)

I've been meaning to add a comment to this thread along the lines of 'once you get relations involved everything turns to crud' but it's probably too late now!

[FunkyDexter]

assuming he's actually dead, of course!

Until you dig him up he's both alive and dead.