Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    17

    Unanswered: Question about using conditional relationships

    How would you define a key relationship that would allow a condition to tell the relationship where to look for the primary key?

    Say I have two tables, TableA and TableB. Both tables represent two very similar data types. There is an additional table (TableC) 'below it' (if you imagine the database's diagram) that they share to hold a variable number of additional properties for each super type. TableC has two columns that allows the code to tie one row in TableC to one of the other higher level tables (A or B). Since the key columns in Tables A and B are not GUID's (randomly generated varchar's...hindsight is 20/20), I can't just put a relationship on the FK coloumn in TableC as there may be the same key in both A and B.

    As a better example, image that Tables A and B represent different types of threads in a forum (say one for SQL and one for a programming language), and TableC would represent attachments or something. Both Tables A and B could have rows in TableC, so TableC has another column called 'ThreadType' and SQL would first look at the ThreadType to determine what table to read from to follow the FK pointer.

    If this isn't possible, could someone point me in a direction so I can clue myself in on how best to handle such situations? Would it be best to use a child table for both A and B (in my application, this would lead me to eventually have around 15-20 'TableC''s), create a column for each FK (again, this would lead to somewhere around 15-20 columns in the table that would mostly be null values), or would it be best to convert Tables A and B to use GUID's instead of randomly generated varchar's? Option 3 is the last ditch way as I would have to change a large number of classes to handle the GUID's.

    Any help is appreciated!

    Lane

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Thinking about that makes my head hurt!

    My first approach would be to see how tough it would be to eliminate the duplicate keys. Finding and eliminating them isn't hard at all, but there can be a lot of associated stuff that you need to wade through that might prohibit it.

    The next thought that jumps to mind is writing a trigger to simulate a "floating FK", although that's usually more work than just fixing the dupes.

    There are other ways to skin this cat, but those are my first thoughts.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hey, don't feel bad. Some people NEVER realize the benefits of GUIDs....
    y'all...

    Actually, most of your issues arise from your (or somebody's) initial decision to split tables A and B in the first place. Splitting similiar (or even identical) datasets into separate tables is for some odd reason a very common practice among newbie database developers. Especially those with VB coding background. It does not surprise me that you anticipate 15-20 such tables. You can probably expect more, because the only way to scale up your application may be to add more tables. Trust me, this becomes an administrative nightmare eventually.

    Take a fresh look at your design. Isn't it POSSIBLE to combine the data in tables A and B into a single table, with perhaps an additional column to define the type of record? This is generally preferable for all but the largest databases, even if it means some fields are not used by all records. Your issues with table C will evaporate, as it will only be linked to one parent table with no duplicate keys.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Aug 2004
    Posts
    17
    I think my example was a little lacking, but Blindman you are correct that this is my first major attempt at tackling a SQL database from schema design on. I have done a lot of work with LDAP schema design, and doing what I want to do is fairly easy in LDAP (I could just use a DN to refer to the parent 'table', but I suppose that would be analagous to a GUID), so I guess I got lazy and didn't realize I was shooting myself in the foot until the damage was done. Now I am stuck in a way...

    Let me explain a little further what I am trying to do. To give a specific example, Tables A and B are both types of posts in a classifieds system I am working on and TableC are 'applications' that other end users have submitted to those posts. While there are a couple of identical or similar columns in the two tables, the vast majority of the columns are not even close to one another and I think that joining the post tables would cause more frustration and performance problems later on when this thing has a couple of hundred thousand records in it (I am not sure I even could, one of the tables is already flagging errors that it is too large and I may have to do a vertical split to get it to work properly). By cutting each post type into a respective table, I can split the load a little. Since this is the backbone of the system, I don't mind having a bunch of tables for the posts, I was just hoping to condense the apps into one table so I could save time coding (I know, lazy programmer, but I have deadlines looming and any code I can reuse saves me on asprin). With the applications being identical across the board except for the keys, I was trying to push SQL a little too hard into the OOP model.

    But now that I just put that thought into words, I now realize I am contradicting myself by putting all the apps into one table as there will be many apps per post....so any performance I gain from splitting the posts I would lose several times over when I have to load the apps.

    I guess I have been looking at this problem too long and I am starting to overthink it. I would still like to know from more seasoned DBA's how they would handle this scenario (or keep themselves from ever getting into it), assuming the tables were smaller, just for my own info, but I think I am going to make one TableC for each type of post. While not ideal, it looks like its going to be the easiest way to sleep in the bed I made myself. = (

    And I was *SO* close to going with GUID's. To all the other newb's out there, take the time and set them up. You will be glad you did, trust me! ; )

    Thanks Pat and Blindman for your answers.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, a caveat here. We just had a heated discussion on this forum a week or so ago about the various merits and demerits of GUIDs. I happen to use them a lot, but other people feel differently so don't kick yourself for not employing them.

    Note: a table with 100,000 rows rows should not be flagging errors because it is too large. What kind of errors are you seeing.

    If you want general help with your schema design, then post the tables and fields that you require to support your application.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Aug 2004
    Posts
    17
    Sorry to bring this back up after letting it sit for so long. I got wrapped up in other things and am just now getting a chance to come back.

    Blindman, the errors being flagged are in the size of the table's schema, not the size of the table itself. I had the table using a nvarchar(3000) that was causing the page size to exceed the 8000k mark. I have since corrected that by making the field a text field. I would have liked split up the table, but that would have required some redesign in the code that I didn't/don't have time for. Its fixed for now, but I am watching it closely as I am sure performance will be a problem, but this goes to show why I can't condense all the tables into one table so I can apply the relationships ideally, the ones that are causing this problem are quite large on thier own, condensing them would probably fix the immediate problem but would cause more...

    The question I still have though is more of a "good practice"-type question. I am trying to figure out a way to handle having one table that has foreign keys that will exist in one of multiple tables. For example, let me jump back to my earlier example of an online post forum. Its not a good real world example, but its easier to explain than what I am actually working on.

    Say that posts for SQL server and DB2 are stored in different tables (I know that would be bad in real life since the two would share a lot of columns, so just assume they are just different enough to warrant seperate tables), and each table would have a relationship to a user table 'higher' in the relational diagram. Also, the PK for the two post tables is a random number that is checked before insertion to prevent duplicate key errors (surrogate key?), but the check is only performed on the table for that particular post (i.o.w., the value for a new PK in SQL Posts are not checked against the PK's for DB2 Posts, and vice versa, so duplicates are not only likely but common) . No problems up to this point (functionally), the PK in the user table can be used as a FK in either post table and deletes can be cascaded down to the posts if a user is deleted without a hitch.

    Now, lets assume there is one table for all replies to a post, irregardless of its a SQL or DB2 post. The reply table has its own PK value that is similar to the PK's for the the posts (random number checked before insertion). In the reply table is a column to denote the post ID and another column to denote the post type (SQL or DB2) that the reply applies to. You can't define a column in the post table to hold the key of the reply becase there will be multiple replies or maybe none at all.

    So the table's schema's look something like so:

    User Table [PK - UserID]

    SQLPost [PK - PostCode, FK - UserID]
    DB2Post [PK - PostCode, FK - UserID]

    PostReply [PK - LogID, FK - {PostCode??, LogType??}]

    If you delete a user object, then the associated posts would delete fine. But when the posts are deleted, the replies are left in the database and RI is now broken.

    Can you do a combined key like I have in the PostReply, or can you define a way to say, when deleting a Post, to look in the PostReply table for any rows that PostCode=@PostCode AND LogType=@PostType to cascade the deletes down to the PostReply table and just delete the rows that apply? In other words, how do real DBA's handle this scenario (or keep themselves from getting into it)?

    I can do this type of relationship programmatically just fine, but I would like to define the relationships in SQL as well for the sake of redundancy, plus I would eventually like to get away from applying such relationships programmatically and let SQL do all the work. What I don't want to do is define a table for each Reply type. This is why I was saying using GUID's would make sense, but since others think there is a better way to do it, I would love to hear them as it may save me from having to re-invent something...

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    1)
    tableC(tc_id, fk_ta, fk_tb) + the appropiate constraint to ensure:
    - either fk_ta or fk_tb must be NOT NULL but not both
    2)

    Super table : ST_POST
    tableA(ta_id, fk_st_POST) *
    tableB(tb_id, fk_st_POST) *
    tableC(tc_id, fk_st_POST)

    * depends on sub-type implementation
    Last edited by r123456; 12-07-04 at 00:47.
    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
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by blindman
    Hey, don't feel bad. Some people NEVER realize the benefits of GUIDs....
    y'all....
    Well, there is place and time for everything, including GUID's, so don't generalize it.
    Quote Originally Posted by blindman
    Actually, most of your issues arise from your (or somebody's) initial decision to split tables A and B in the first place. Splitting similiar (or even identical) datasets into separate tables is for some odd reason a very common practice among newbie database developers...
    Man, have you ever worked with VLDB's? I mean EVER?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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