Re: Dreaming About Redesigning SQL

Originally posted by Mike Preece

> andrewst <member14183_at_dbforums.com> wrote in message
> news:<3556922.1067944594_at_dbforums.com>...

> > First of all, as Bob pointed out yesterday, the relational model
> does

> > allow for relation-valued attributes, so that all the phone
> numbers for

> > a person could be stored within the single person record.
> However, that

> > would not be the "traditional" relational approach to this.

>

> Why not? Is there something in relational theory that says that would

> be wrong in some way?

>

See Jonathan's answer to this - it's better than I could express it. My view is that embedding relations within relations just makes life more complicated, e.g. if you want a "phone-centric" look at the data you have to un-embed all the phone data to do it. I have never had a wish to embed relations within relations; I imagine that if I did want to it would be because I considered the embedded relations to be atomic, i.e. I would have no interest in inspecting their contents - much as I might have an attribute whose contents were a bitmap image, and would not know or care how it was structured. Maybe if I was using a table to store backup copies of other tables or something... I don't know.

>

> Could or would you also store data (type & usage) associated with each

> person's phone number in a relation-valued attribute within the

> Person's record?

>

You could and would.

>

> > Now, if a person has 1 phone, we will insert 1 record into this
> table;

> > if she has 2 we will insert 2; if none, we will insert none.
> There is

> > no need, as you say there is above, to "reserve nulls for
> everyone else

> > if just one person has more than one phone". That would only
> apply if

> > we had used a separate COLUMN in the person table for each phone
> type

> > (home_phone_no, work_phone_no, mobile_phone_no) - which
> presumably would

> > be the same in Pick if you chose that approach.

>

> OK. I guess I should have said column instead of row. I'm not used to

> thinking in terms of columns and rows - especially as what would be a

> column in a table in relational terms equates more to an attribute in

> an item in Pick, and we see attributes as rows across the screen when

> we look at an item. It can get confusing.

>

Yes. In fact "column" is the SQL term for what relational theory actually calls an "attribute".

>

>

> > (Aside: some people will splutter at me mentioning Oracle (or
> any other

> > SQL DBMS) in a discussion about the relational model. Fair
> enough.

> > The pure relational model is better than SQL, and SQL is better
> than

> > the rest.)

>

> And I just have to say that, from my admittedly limited understanding,

> they all seem to focus on relatedness and turn a blind eye to

> relevance.

>

Agreed. This "relevance" concept doesn't concern us, since we consider all data to be potentially relevant, and we store all data in a form that is optimised for access. Note: I am using "optimised" in a logical sense here: i.e. it is just as easy to "find all phones for person X" as to "find all persons for phone Y". Physical optimisation is a separate issue and is NOT compromised by the logical model; as I have said before, if we decide that the data should be physically biased towards the first type of query, we can alter the physical storage appropriately, e.g. cluster the phone data by person. If for some strange reason we later decided we had been wrong and that the phone was more "relevant" than the person, we could modify the physical storage so that the phone/person data was clustered by phone - and not a single line of code (i.e. SQL) would need to be changed or recompiled.

>

> > So your point about saving disk space is debatable at
> least.

>

> Removing trailing nulls does not provide grounds for the above

> statement. I have yet to see any evidence or explanation to show how a

> relational database could possibly occupy less space than an

> equivalent amount of data in Pick - even if there was an abundance of

> numeric data. My experience tells me the reverse is in fact the case.

> How could it be otherwise when all data in Pick is variable length? If

> you have 35 characters for a surname field to store "Andrews" then in

> Pick it will take up 7 bytes (plus one for a system delimiter). When

> you think about all the extra tables and all the fixed length

> fields... I'm going to take a lot of convincing I'm afraid.

>

You have just described the characteristics of the SQL VARCHAR2 data type, almost. If I define attribute/column NAME as VARCHAR2(35) and store "Andrews" in it, it will occupy 8 bytes (in Oracle at least): 1 for the character count and 7 for the individual characters. Of course, the relational model per se has nothing to say about this, as it works with any data types that people care to define.

--
Posted via http://dbforums.com