Page 1 of 2 12 LastLast
Results 1 to 15 of 30

Thread: RE: Boring

  1. #1
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778

    RE: Boring

    Hi,

    I am interested in opinions on the following,

    main(product_id, short_text, price, type)
    productTypes(type_id, type)
    productTables(type_id, tableName)
    productA(product_id, field1, field2)
    productB(product_id, field1, field2)

    To retrieve all products of type 'A', one must know the table name, in this case 'productA'.

    Here is one method.

    Create a table that contains the table name that corresponds to each product type, thus the stored procedure only needs to recieve the type_id which can be used to obtain the name of the respective table.
    Last edited by r123456; 07-15-04 at 12:16.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I guess the "Re: Boring" title means I should respond

    Well, this is an interesting idea - sounds like something a C programmer would come up with - a mixture of data and metadata.

    I'm not sure how much is gained though. Presumably tables ProductA and ProductB would in reality have a different set of columns - otherwise, what's the point of the separate tables? So your stored procedure cannot use dynamic SQL, instead it will have to do something like this:
    Code:
    SELECT tablename FROM producttables WHERE type_id = :type;
    IF tablename = 'ProductA' then
      SELECT Afield1, Afield2 FROM ProductA WHERE product_id = :id;
    ELSIF tablename = 'ProductB' then
      SELECT Bfield1, Bfield2, BField3 FROM ProductB WHERE product_id = :id;
    ...
    END IF;
    That is no better than
    Code:
    IF :type = 'A' then
      SELECT Afield1, Afield2 FROM ProductA WHERE product_id = :id;
    ELSIF :type = 'B' then
      SELECT Bfield1, Bfield2, BField3 FROM ProductB WHERE product_id = :id;
    ...
    END IF;
    Next I suppose you could suggest having another "metadata" table like this:
    Code:
    create table product_columns
    ( type_id, column_name,
      primary key (type_id, column_name)
    );
    Then you could create the SELECT statement dynamically. But then what would you do with the variable number and types of values returned? If feel another IF statement looming...

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    Presumably tables ProductA and ProductB would in reality have a different set of columns - otherwise, what's the point of the separate tables?
    that's the part that i don't understand too

    if they look the same, they should be combined

    if they look different, the whole scheme is wonky
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Indeed, they would be different.

    master(id, short-text)
    productA-Book(id, book-specific-attributes)
    productB-DVD(id, dvd-specific-attributes)

    I would like to point out that I do not especially favour the method I supplied, rather am hoping for someone to point out that it is not required.

    The problem I am trying to solve is related to the Data Access components of an application. Granted, application design does not determine database design.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r123456
    Indeed, they would be different.

    master(id, short-text)
    productA-Book(id, book-specific-attributes)
    productB-DVD(id, dvd-specific-attributes)

    I would like to point out that I do not especially favour the method I supplied, rather am hoping for someone to point out that it is not required.
    Well, my post tried to show that in this case the method doesn't buy you anything - you still need separate code for each table type, whether you store the table names in another table or not. Something that creates extra work without any benefits is not required, right?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, books and dvds, that's a much better example

    this is a rather common data modelling problem (subtypes and supertypes)

    the place from which to consider your design options is the application

    the application may ask "show me all things" but under the covers, the logic must retrieve and format the results for books and dvds separately

    for example, an application screen may list several lines showing all books and dvds in inventory, but the lines listing the books are formatted differently from the lines listing the dvds

    so the app already has to have "if" logic in it

    one way around this is a UNION query, but that forces unlike data to share the same columns, which you could also achieve by simply squeezing both types into a common table to begin with

    to me, the subtype/supertype model is easiest with a single table, many common columns, a few specific columns to each type

    e.g. name, title, published, datepublished, inventoryquantity, bookcover, dvdformat

    there is still some "if" logic required, but it's not as bad as separate tables

    consider what searches (e.g. search all inventory for "planet of the apes") would look like in the multiple-table model versus the single-table model

    lots of examples where the single-table model is better

    and very few where the multiple-table model is better

    finally, let's talk about nulls

    the bookcover column would be null for dvd rows, and the dvdformat column would be null for book rows, right?

    pretty neat, eh?

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

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Rudy, excellent. However, do we now advocate denormalizing the data all into a single table to make the application's task easier. This is the other debate. An application with N methods or one denormalized table to assist the application.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r123456
    Rudy, excellent. However, do we now advocate denormalizing the data all into a single table to make the application's task easier. This is the other debate. An application with N methods or one denormalized table to assist the application.
    On a point of order here, it would not be "denormalised" - since every non-key value will be dependent (solely) on the key, and column values are still atomic.

    I think Rudy has already spelt out the pros and cons. I agree wth him: a single table with some columns being populated only for certain subtypes is the easier approach, though less theorerically "pure". That is, provided there are not so many subtypes that you end up with an unweildy 1000-column table. But then, if there were that many subtypes, handling a table for each would be a nightmare too...

    If you do have hundreds of subtypes, I'm not sure that either approach is satisfactory. Perhaps in that case a rethink is required: is it practical and necessary to break down the data so finely? We would be talking about 100s of variations on the input form, 100s of variations in reports, etc. Maybe that would be time to say "enough: we will hold all the common attributes, plus a few frequently used ones, and then give you a big text field in which to record other details."

    Luckily, I have never been in this situation, but I have seen many posts about it.

  9. #9
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    This is the exact problem I have. I think i'm going to stick with many small tables rather than one BIG table with all the columns, for some reason it doesnt seem right to have redundent columns for subtypes.

    If you guys come up with a brain wave, I'd really like to here about it. I'm punching walls on this.

  10. #10
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Jwab, I would not say that the columns are redundant. All the columns would be required, just not applicable to each row. To expand on my original method:

    master(id, search_text)
    types(type_id, select_view_def)

    Then (simplistic example),

    1. Select distinct view_def from types
    2. Retrieve appropiate data from each table using master.id

    The only reason I thought of this was to avoid a possible situation that consisted of for example, 15 sub types with a total of 60 attributes.
    Last edited by r123456; 07-15-04 at 13:19.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jwab
    If you guys come up with a brain wave, I'd really like to here about it.
    i have a great suggestion

    put together a data access strategy for each model (one supertable with columns for all unique attributes, some of which will be null on some rows, versus multiple subtype tables, none of which will have any nulls)

    consider the books and dvds example

    here's the gedankenexperiment, and i specifically want you to think about what code would be necessary to pull this off --

    present an inventory list of books and dvds in title sequence

    the code involved will be a combination of sql and application scripting/logic

    remember, it's got to be in sequence by title, and yet be a mix of books and dvds

    think about the sql (one table versus many) and also the application logic

    fun, eh?



    bottom line: unless i have ample reasons not to, i'm gonna go with the single table approach every time

    a single query, and you can do the ORDER BY easily

    with multiple tables, you either have to "combine" the queries with a UNION, or else retrieve them separately and (ugh!!) sort them in your application script

    you see, data modelling is not entirely about "the best structure"

    you also gotta get the info back out easily
    Last edited by r937; 07-15-04 at 22:34.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2004
    Location
    UK
    Posts
    43
    Ok I see how it would be so much easier to extract infromation from the db with only one table looking after all the different types of products.

    BUT with my problrm I think an alternative method may need devising. You see your single table idea is probably ok of the DVD, Book scenario, because you dont need to track each indiviual DVD or book. With the mobile phone problem, we need to record each individual mobile phone. So instead of having a product list of a few 100, or even a few thousand. This db needs to track a few 10's of 1000's which increase's every day, so in 2 years time, where we still need to know where a particular mobile is, the table could have nearly 100,000 rows,which will increase to millions as the business grows.
    This would create one MUMMA of a single table. So what performance hit are we taking in the select, and update routines.
    If your certain that the single table is still the way togo, then I will redesign my scheme (after all, I need to get this right).

    On a slightly alternative approach, I've been reading a bit on OO db design. It's abilty to handle real world entities and recursive queries is appealing.
    Although I'm still not sure to the full extent or it's usefulness.

    I've just jotted this down, but if I went the single table route this is what it would look like.


    phone/sim(stock_code, make, model, IEMI, sim_num, mob_num, cost_price, supplier, location, state, date_in, date_out, date_sold, cust_id, staff_id, tariff, price_paid, RA_num, rtn_date, discon_date, con_err_date, resell_date)

    That is all the information required in the life of a phone or SIM. From that other tables would be generated, for sales, commissions etc

    There will be alot of data replication for fields such as stock_code, make, model, price, location, state, dates...

    but maybe this doesnt matter, just gotta make sure fields have lots of constraits when changeing a record.

    Is this an applications for super/sub typing or not?

  13. #13
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The row count of a table is not an issue. My original point was to devise an alternative for a situation where the total column count would exceed, for example 50 columns.
    Last edited by r123456; 07-16-04 at 08:14.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I don't know which DBMS you (jwab) will be using, but some allow you to "partition" tables to deal with concerns about managing large tables. Thus you may get physical separation of phones from SIMs (for example) without having to have logical separation.

    Having said that, a few million rows is not considered alarmingly "big" for an enterprise DBMS like Oracle, SQL Server or DB2 these days.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    column count doesn't matter either, if the table is normalized

    oh, wide tables may not perform as well as related narrow tables, but that isn't the issue here

    a good DBA can take a wide table and make two 1-to-1 related narrow tables, such that you (the user or application) would never know the underlying physical data structure was actually two narrow tables being joined

    that's within the realm of physical implementation and can be done later for performance reasons

    would the DBA attempt to consolidate a subtype/supertype structure into one signle table (or two narrow ones if the single one is too wide)?

    probably not, no DBA that i ever met would've dared it
    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
  •