i have a large database that handles almost 10 lac consumers...the interesting thing is none of the tables in here are related...i.e a relational model is not followed....the database is used just as a repository citing performance gains and maintenance simplicity...the result being too many redundant data.
most of the neccessary relations between the entities are captured in a seperate interface in code.. again for performance...
is this design better than a relational database model....?
Ten lahk (one million) customers is good sized. Certainly not small, but not huge either.
Keep in mind that asking this kind of question in a database forum is going to get database centric answers. With that in mind, using the relational model is going to cost you some time up front to clean up your data, but it will save you orders of magnitude more time going forward because using the relational model will give you much better quality data which will lead to much better quality information. That will save you (as a geek) and your company from wasting time and effort trying to get the right answers out of many possible answers (only some of which will be correct).
I would say the way tables are designed was based on the website ui...watever fields a particular ui displays comprises of one table....another set of ui fields comprise of another table. Although not all tables are designed such but this concept domainates ..which they say has performance gains.....redundancy is ample....jus gimme the disadvantages of such design..
The relational model advocates only one relation with one entity. You could have many relationships but with different entities but only one relation with one entity. If a relationa less model is in use it will cause.
Given 150 entities, and relationships between them defined at the Form level and not at the Base Tables levels, you have to write enromous code to support them. Further more modifications are very complex and error prone.
Customer ID - PK
Order No - PK
Customer ID - PK
There is one-to-many relationship between CUSTOMERS & ORDERS. And the relationship is only one. It is not logical to have more than one relationship. Does not make any sense. However the CUSTOMERS table can have another relationship with another table such as
Contact ID - PK
Contact Telephone Number
CUSTOMER ID - FK
Now CUSTOMERS has one-to-many relationship with ORDERS and CUSTOMERS also has one-to-many relationship with ORDERS as below
Imgaine trying to write code when there are no relationships. To enter a Order for a Customer the code has to look up the in CUSTOMERS TABLE, find the customer and than ORDERS TABLE
Perhaps JAA149 is refering to the Principle of Orthogonal Design (McGoveran and Date), which roughly speaking states that no more than one relation should be permitted to represent the same (or overlapping) sets of propositions (aka "entity types" or "entities" in E/R modelling terms).
POOD is a formal technique and a recommendation, not an absolute rule. It's more than just "stuff and nonsense" though!
what about the classic "department has employees" and "employee is department manager" -- same two entities, two different relationships
I was talking about relations, not relationships. You seem to think ignorance is a virtue but I think even you know that a relation and a relationship are different things. So maybe you have something useful to contribute to this thread... ?
whoops....it seems i phrased it incorrectly...i meant there r many relationshps with different tables..n yes the code i meant are nothin but sql statements..theyll b doin the lukup and retrieval of records...only thing theyll be abstracted out from the business logic and given an iterface...neways thanx u all...i think i found the paper i was lookin for....."THE COSTLY ILLUSION: NORMALIZATION, INTEGRITY AND PERFORMANCE"....
Here is where "stuff & Nonsense" come to play. I am sure that you "have no interest in reading date or pascal" because they are over you head. You may just be the guy who thinks Relational Database and Database Relationals are one and the same.
As far as the classic "department has employees" case, the relationship are two indeed. But one relationship is with the table it self. What is the name of this kind of relationship? I forgot. Any Body? But we could express it using "Rabbit Ears"
and no thanks, i have no interest in reading date or pascal
That's a pity, Rudy. Date is a great writer (and speaker) on the foundations of the relational model. I have learned a lot from his books and one of his seminars, even though it cannot all be directly applied in SQL databases.
Pascal is harder to defend: much of his technical writing is sound, but it is dwarfed by the far greater amount of his writing which is aggressive bordering on psychotic...