Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2009
    Posts
    66

    Unanswered: Design questions

    1) How do you decide between horizontal and vertical partitioning? For an example, take a social networking applications friend feature. You can have a select number of top friends. Should these be columns or rows?

    2) Is it possible to use user-defined functions in the where clause or does it still cause the optimizer to generate non-optimal plans (sql 2008)?

    3) For stuff like "edited_date" and "editor" columns, should it be null or have default values from the "created_date" and "creator" columns? I know you can compare created_date = edited_date and figure out if edited_date is suppose to be null. However, it is sort of a logic error - how can a member that just signed up have a modified date also!

    4) Take a forum data model. Forums would be aware of topics, topics would be aware of forums, messages are aware of topic....should it be aware of forums? (this would require all forum composite keys to be in the messages table as a foreign key)

    5) Should you store SPs based on CRUD or seperate them? (For an example, InsertUpdateDeleteMember vs InsertMember, UpdateMember, DeleteMember...the logic can get complex in the god procedure)

    6) When using natural keys (forum scenario), should you use username or email? I think email change would be a buisiness logic change while username would probably be aesthetics.

    7) I read an article where it says that primary keys are not required if you have unique constraints on the keys that define the data entity. However, isn't a table not a table if it doesn't have a primary key by definition???

    8) Regarding candidate keys, sometimes a table will require 2-4 candiate keys which become the primary key. This would mean that any foriegn keys would also have those 2-4 candidate key references. This would take a lot of space but how is it in performance? (celko mentions minimal hashing algorithm etc)

    9) Cyclic Foreign Keys - If you have a table with two foreign keys back to the primary key table, you cannot use cascades because SQL Server 2008 complains of cyclic foreign keys. All other databases (probably even access) support cyclic foreign keys! This defeats the purpose of having the DDL doing "most of the work" in terms of cascades. Any workarounds other than SPs and triggers?

    10) User defined types - Purpose is to define a "type" with custom constraints and use it in tables. Anytime you want to change the type, just alter the type. Nope, not in SQL Server 2008. In SQL Server, you have to get rid of the references, then drop and create the new type and go to each table and manually assign the type. All other database vendors have it automated. This is just plain stupid. No wonder celko refers to SQL Server 2008 as the "lesser sql". Any workarounds?
    Last edited by sqlguru; 06-21-09 at 14:31.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1. rows
    2. still causes the same issues
    3. I would use null
    4. no
    5. separate
    6. depends
    7. unique not null is physically and logically the same as primary key
    8. this doesn't make sense to me
    9. no
    10. no
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2009
    Posts
    66
    What I mean by question 8 is that when the forums have a primary key consisting of two candidate keys (forum name and forum created date), you have to copy the two candidate keys into the topics table as well because they are foreign keys.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I think you are misunderstanding what a candidate key is. Look it up on Google.
    I think you mean a primary key that is a composite of two or more columns. Yes, that is correct if you use natural keys. You can avoid this by using what are called surrogate keys instead. In terms of performance, it depends on the specifics of the queries. Broadly speaking, surrogate keys usually perform more quickly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2009
    Posts
    66
    Is this correct?

    compound key - an artifical key that is a concatenated value consisting of multiple natural keys
    composite key - a multi-column primary key
    candidate key - a column that is qualified to be a primary key (uniquely identifies the row) and if it is not used as a PK, it becomes a alternate key.

    I know what a surrogate is, but I prefer to use natural keys even if it requires composite keys.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    more or less.
    I wouldn't worry too much about the definition of a compound key - it is certainly not made up of concatenated values.
    A composite key is not necessarily a primary key.
    A candidate key can be made up of 1 or more attributes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    1. rows
    2. yes it is possible. depends
    3. Disallow nulls. Use defaults and triggers. My opinion.
    4. Stay away from composite foreign keys.
    5. Separate. Insert, Update, Delete, and possibly "Upsert".
    6. Username. People may share emails.
    7. A table is a table regardless of whether it has a primary key. Its just a wobbly table.
    8. Stay away from composite foreign keys.
    9. What's wrong with triggers?
    10. Yes, User-defined types are poorly implemented and misleading. Don't use them.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    2. yes it is possible. depends
    In the WHERE predicate? Rly? Example, plz
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by blindman
    1. rows
    2. yes it is possible. depends
    3. Disallow nulls. Use defaults and triggers. My opinion.
    4. Stay away from composite foreign keys.
    5. Separate. Insert, Update, Delete, and possibly "Upsert".
    6. Username. People may share emails.
    7. A table is a table regardless of whether it has a primary key. Its just a wobbly table.
    8. Stay away from composite foreign keys.
    9. What's wrong with triggers?
    10. Yes, User-defined types are poorly implemented and misleading. Don't use them.
    Celko once said that a table isn't a table without a primary key by definition. Dr. Codd said the same thing but later realized that a key is a key.

    Here's another article pointing out you do not need primary keys:David Portas' Blog : Down with Primary Keys?


    Regarding composites, celko mentions vldbs like teradata and "minimal hashing" algorithms and due to advancing technologies (multiproc and ssds) which means that the length of the keys do not matter anymore.

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Yes the concept of the "primary key" is totally superfluous in the relational model. A fact that is too often forgotten. To aid human perception and understanding it frequently helps designate one key as special in some way. There's no harm in that if you find it useful but you are not and should not be compelled to work that way.

    In SQL keys of any kind are optional and sometimes not supported at all. Yet SQL makes it extremely hard to work with tables without keys. It's almost never a good idea to create tables without keys in SQL (barring the limitations of SQL DBMSs that often restrict where you can use keys).

    A primary key is always a candidate key, no more and no less. So to speak of a "primary key consisting of two candidate keys" is completely impossible and wrong. I'm speaking of the relational model here, not SQL's misnamed and misbegotten creation called a "PRIMARY KEY".

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by sqlguru
    Celko once said that a table isn't a table without a primary key by definition. Dr. Codd said the same thing but later realized that a key is a key.
    I don't believe either of them are active members on this forum...

    Quote Originally Posted by sqlguru
    Regarding composites, celko mentions vldbs like teradata and "minimal hashing" algorithms and due to advancing technologies (multiproc and ssds) which means that the length of the keys do not matter anymore.
    The length of the key is not the reason I discourage composite keys. Referential integrity and cascading operations are the concern.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by blindman
    I don't believe either of them are active members on this forum...


    The length of the key is not the reason I discourage composite keys. Referential integrity and cascading operations are the concern.
    What I meant to say was compound keys (attributes that uniquely identify an entity and also functions as a superkey) however composite keys work fine too. Why would referential integrity and cascades be of concern?

  13. #13
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by dportas
    Yes the concept of the "primary key" is totally superfluous in the relational model. A fact that is too often forgotten. To aid human perception and understanding it frequently helps designate one key as special in some way. There's no harm in that if you find it useful but you are not and should not be compelled to work that way.

    In SQL keys of any kind are optional and sometimes not supported at all. Yet SQL makes it extremely hard to work with tables without keys. It's almost never a good idea to create tables without keys in SQL (barring the limitations of SQL DBMSs that often restrict where you can use keys).

    A primary key is always a candidate key, no more and no less. So to speak of a "primary key consisting of two candidate keys" is completely impossible and wrong. I'm speaking of the relational model here, not SQL's misnamed and misbegotten creation called a "PRIMARY KEY".
    I have to agree. Every query I have written uses candidate keys for filter clauses to uniquely identify an entity or a set of datat, it doesn't matter if a primary key was used or not.
    In SQL Server, you need primary keys if you wanted to implement DRI. In the "true relational model", you don't.

  14. #14
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by sqlguru
    In SQL Server, you need primary keys if you wanted to implement DRI.
    No you don't. SQL Server in common with other SQL DBMSs allows you to declare referential constraints ("FOREIGN KEYs" in SQL parlance) on any superkey(s) of the table. Each FOREIGN KEY just has to match the column list of some UNIQUE constraint. You are not limited to just the one key and not just to the "primary" one.

    Oddly, SQL Server also allows you to declare a FOREIGN KEY against an index! Try it if you don't believe me.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by sqlguru
    Why would referential integrity and cascades be of concern?
    Because each new level of depth in the database schema requires an additional column to the compound key, and updating at a higher level may cause conflict cascades at lower levels. And complex schemas may have split cascade paths which make it even more challenging. Its just a mess that is easily avoided if you separate the physical representation of your data from the logical representation by using single-column surrogate keys.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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