Page 1 of 5 123 ... LastLast
Results 1 to 15 of 61

Thread: database design

  1. #1
    Join Date
    Dec 2008
    Posts
    6

    database design

    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....?
    Last edited by mickeystaines; 12-12-08 at 01:08.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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).

    -PatP

  3. #3
    Join Date
    Dec 2008
    Posts
    6
    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..

  4. #4
    Join Date
    Dec 2008
    Posts
    27
    Hai are you not the guy who posted the same question on another web site. I though I answered you question and listed all the disadvantages there.http://forums.databasejournal.com/sh...ad.php?t=49721
    Last edited by r937; 12-14-08 at 09:06.

  5. #5
    Join Date
    Dec 2008
    Posts
    6
    yeps.. thanx a lot....but one more thing...this database contains around 150 entites... plus their varoius relations between each other...wldnt the relationless design be better?
    Last edited by mickeystaines; 12-15-08 at 04:25.

  6. #6
    Join Date
    Dec 2008
    Posts
    27
    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.

    1 - Update analomies
    2 - Deletion analomies
    3 - Insertation analomies

    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.

    Lets say

    CUSTOMERS
    Customer ID - PK
    Customer Name

    ORDERS
    Order No - PK
    Order Date
    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

    CONTACTS
    Contact ID - PK
    Contact Name
    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

    Click image for larger version. 

Name:	Customers & Orders & Contacts.jpg 
Views:	747 
Size:	31.3 KB 
ID:	9042

    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JAA149
    The relational model advocates only one relation with one entity.
    stuff and nonsense

    please cite a reliable reference for this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by r937
    stuff and nonsense

    please cite a reliable reference for this

    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!

    See "Date on Database"
    http://www.apress.com/book/view/9781590597460

    Also:
    http://www.dbdebunk.com/page/page/622331.htm

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what about the classic "department has employees" and "employee is department manager" -- same two entities, two different relationships

    and no thanks, i have no interest in reading date or pascal

    also, please note that you've broken dbforums' dbdebumph law

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Is the acronym really POOD?

    *stiffled giggles*
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by r937
    what about the classic "department has employees" and "employee is department manager" -- same two entities, two different relationships
    Rudy,
    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... ?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dportas
    So maybe you have something useful to contribute to this thread... ?
    not any more, no

    i can see i'm out of my depth

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2008
    Posts
    6
    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"....
    Last edited by mickeystaines; 12-16-08 at 02:57.

  14. #14
    Join Date
    Dec 2008
    Posts
    27
    Man this r937 guy is real or what? Looks like he has deep mental problems. He has these kinds of remarks for every post.

    r937 - Apart from breaking Forums rules, first learn Social etiquette. Finish one thread first and than go to any new thread to make some new hazards.

    Look at this one where r937 is simply refuses to accept that he does not know the answer. If it's too long for other guys just read the last three posts.

    http://www.dbforums.com/showthread.php?t=1636360

    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"

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •