Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2012
    Posts
    4

    Red face Quotes table schema ?

    Hello,
    Sorry for the previous message..
    ok, now as i have started designing the database i come across quotes(customers can view quotes sent by suppliers), i have 2 tables(i suppose) :

    Quotes
    ------
    quote_id (primary key)
    client_id - so you know who the quote belongs to.
    date - when the quote was generated


    Quote_Items
    -----------
    quote_item_id (primary key)
    quote_id (foreign key to Quotes.id)
    product_id (foreign key to you products)
    single_item_cost (copy from products so that if the product price changes the quote won't change)
    quantity (multiply by single_item_cost to get total cost for this quote_item)
    ----------------
    what is the quote_item_id ?
    also, i dont get single_item_cost (copy from products so that if the product price changes the quote won't change)
    quantity (multiply by single_item_cost to get total cost for this quote_item)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vipa13 View Post
    what is the quote_item_id ?
    it is unnecessary... removes it


    Quote Originally Posted by vipa13 View Post
    also, i dont get single_item_cost (copy from products so that if the product price changes the quote won't change)
    quantity (multiply by single_item_cost to get total cost for this quote_item)
    what do you mean, you don't get it?

    you don't understand the purpose of those two columns?

    the purpose is right there!!

    single_item_cost tells you how much the item costs for this quote

    quantity is how many of the item are in this quote
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    4

    ??

    copy from products so that if the product price changes the quote won't change ??
    we generally update products table if we copy from products then quote cost will change ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vipa13 View Post
    copy from products so that if the product price changes the quote won't change ??
    that is correct


    Quote Originally Posted by vipa13 View Post
    we generally update products table if we copy from products then quote cost will change ?
    that is correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937 View Post
    Quote Originally Posted by vipa13 View Post
    what is the quote_item_id ?
    it is unnecessary... removes it
    It is useful. Leave it in.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    It is useful.
    example, please

    and please don't invent any other tables, use just those two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2012
    Posts
    4

    Lightbulb Quotes - Products Table schema

    Finally,

    quotes: quote_id(PK)
    customer_id(FK)
    date(sysdate)
    quote_items: quote_item_id(PK)
    quote_id(FK)
    product_id(FK) - products table
    single_item_cost(DONT copy from products coz if product cost
    changes this should not get changed?)

    products: product_id(PK)
    product_name
    product_type_id(FK) - prodtype table

    prodtype: product_type_id(PK)
    product_type

    Please suggest if i should change something...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    we gotta teach you how to use the [code]...[/code] tags
    Code:
    quotes:         quote_id(PK)
                       customer_id(FK)
                       date(sysdate)
    
    quote_items:  quote_item_id(PK)
                        quote_id(FK)
                        product_id(FK) - products table
                        single_item_cost(DONT copy from products coz if product cost  
                        changes this should not get changed?)
    
    products: product_id(PK)
                  product_name
                  product_type_id(FK) - prodtype table
    
    prodtype:  product_type_id(PK)
                   product_type
    see that part in red up there?

    where are you going to get the values for that column from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937 View Post
    example, please

    and please don't invent any other tables, use just those two
    Just because something is not required by a logical model does not make it non-useful in a physical model. I've given you examples many times before. Primarily, the consistency of design across tables greatly simplifies coding, both for the interface and for administrative utilities. I can reference individual tables by variables, confident that I can logically derive a method of identifying individual records in dynamic code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Short argument: "Unnecessary" does not mean "useless", and therefor your advice to drop the surrogate key based solely on that criteria was unjustified.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Just because something is not required by a logical model does not make it non-useful in a physical model. I've given you examples many times before. Primarily, the consistency of design across tables greatly simplifies coding, both for the interface and for administrative utilities. I can reference individual tables by variables, confident that I can logically derive a method of identifying individual records in dynamic code.
    those are general platitudes, a dogma dump, a re-spewing of biased opinion

    perhaps a specific example of the "simplified coding" related to the two tables given?

    otherwise, your opinion is worthless
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quickly and automatically generates a script for deleting all orphaned archive records from my (consistently designed) tables:
    Code:
    select	'
    delete	Archive
    from	[' + ArchiveTables.name + '] as Archive
    		left outer join [' + ProductionTables.name + '] as Production on Archive.[' + ProductionColumns.name + '] = Production.[' + ProductionColumns.name + ']
    where	Production.[' + ProductionColumns.name + '] is null
    '
    from	sys.objects as ProductionTables
    		inner join sys.objects as ArchiveTables on ProductionTables.name + '_arc' = ArchiveTables.name
    		inner join sys.indexes as ProductionIndexes
    			on ProductionTables.object_id = ProductionIndexes.object_id
    			and ProductionIndexes.is_primary_key = 1
    		inner join sys.index_columns as ProductionIndexColumns
    			on ProductionIndexes.object_id = ProductionIndexColumns.object_id
    			and ProductionIndexes.index_id = ProductionIndexColumns.index_id
    		inner join sys.columns as ProductionColumns
    			on ProductionIndexColumns.Object_id = ProductionColumns.object_id
    			and ProductionIndexColumns.column_id = ProductionColumns.column_id
    
    --Sample output:
    delete	Archive
    from	[IndexProperties_arc] as Archive
    		left outer join [IndexProperties] as Production on Archive.[IndexPropertyID] = Production.[IndexPropertyID]
    where	Production.[IndexPropertyID] is null
    
    delete	Archive
    from	[ObjectProperties_arc] as Archive
    		left outer join [ObjectProperties] as Production on Archive.[ObjectPropertyID] = Production.[ObjectPropertyID]
    where	Production.[ObjectPropertyID] is null
    ...and so on...
    Satisfied?
    Or would you care to show how this could be coded more simply in a schema that uses natural keys?

    And these surrogate key handles are very useful in implementing a physical design.

    Have fun cascading changes to the natural key of TableA in this schema without using surrogates:
    ........../TableB\
    TableA.............TableD
    ..........\TableC/

    ...or have fun joining TableE to TableF in this schema using natural keys, and have fun managing the size of the non-clustered indexes that result:
    TableA
    ..........\TableB
    ....................\TableC
    ..............................\TableC
    ........................................\TableE
    .................................................. \TableF

    Regardless Rudy, you seem to perpetually labor under the assumption that a database exists for its own sake. But databases are accessed by applications, which are written by developers, who GREATLY appreciate consistency in design.

    Hell, I guess I'd just be happy to hear you explain how "unnecessary" equates to "useless".
    I have a PDA phone that is unnecessary, yet very useful. Forks and spoons are unnecessary, but I presume you do not disdain their use.
    If you had listed a few pros and cons regarding the use of a surrogate key, that would have been one thing, but your ideological hatred of them is irrational, and I could not let the poster walk off with your dogmatic opinion lodged in his head as gospel. Just try to be a bit more even-handed about issues that you KNOW are not cut and dry, and you KNOW are not a matter of general consensus.
    Last edited by blindman; 06-29-12 at 15:33.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    tediously long and irrelevant post
    you never once mentioned the original two tables, Quotes and Quote_Items

    sorry, i will not engage in a holy war with you

    all i asked was an example of "simplified coding" in the context of this thread

    but it seems you have a different agenda

    Last edited by r937; 06-29-12 at 15:38.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Wait...did the Original Poster send you a complete ERD for his system?
    Did he send you a complete set of business requirements for the application, which he neglected to share with everyone else in the thread?
    Did he share his business plan with anticipated IT initiatives and prospective feature enhancements?
    Barring that, how can YOU claim that the surrogate key was useless based on seeing only those two tables? How can YOU advise him to drop that key value without knowing how it might benefit or affect other properties of the system? You can't, and your requirement that any justification for the surrogate key exist solely within the confines of that isolated portion of the schema smacks of desperate apologetic.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Wait...

    ...

    ... [ a lot of irrelevant AD HOMINEM stuff ]

    ...

    ... smacks of desperate apologetic.
    lol

    "smacks of desperate apologetic"

    at least you're entertaining

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

Posting Permissions

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