• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

DB/OO Design question

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    DB/OO Design question

    Just thought I would see what peoples thoughts are on this...

    *Thinks up example*

    lets say you had a database/model with with 4 different tables/entities...

    car boat plane train

    And you add 0 or many String/varchar descriptions to each of them, would you have...

    1. Description table/entity with 4 columns for the FKs and if it was for a car you would have the FK in a car column and the other 3 columns would be null.

    Or would you have

    2. 4 different tables/entitys CarDesc, BoatDesc etc.

    The descriptions are just strings/varchars.

    #2
    OK, I’ll have a go because I am a self confessed non-techie and can make a fool of myself.

    I’d have a separate table for the common fields that would map to the superior class and extra tables for those that were unique to the sub-classes. The PK of all of the sub tables would be the same as the superior one.

    I do it in my DB and I get away with it.
    How did this happen? Who's to blame? Well certainly there are those more responsible than others, and they will be held accountable, but again truth be told, if you're looking for the guilty, you need only look into a mirror.

    Follow me on Twitter - LinkedIn Profile - The HAB blog - New Blog: Mad Cameron
    Xeno points: +5 - Asperger rating: 36 - Paranoid Schizophrenic rating: 44%

    "We hang the petty thieves and appoint the great ones to high office" - Aesop

    Comment


      #3
      I'd use a single table of the descriptions and just use the FK in each of your car boat place etc tables. easy peasy...as HAB has described.
      McCoy: "Medical men are trained in logic."
      Spock: "Trained? Judging from you, I would have guessed it was trial and error."

      Comment


        #4
        See, I would go for 1 everytime (If the entities are a small and never changing group).

        It's 1 table rather than 5, 1 class rather than 5. Simpler queries, faster access.

        (Just came across 2 in here and it got me thinking)

        Comment


          #5
          Originally posted by minestrone View Post
          Just thought I would see what peoples thoughts are on this...

          *Thinks up example*

          lets say you had a database/model with with 4 different tables/entities...

          car boat plane train

          And you add 0 or many String/varchar descriptions to each of them, would you have...

          1. Description table/entity with 4 columns for the FKs and if it was for a car you would have the FK in a car column and the other 3 columns would be null.

          Or would you have

          2. 4 different tables/entitys CarDesc, BoatDesc etc.

          The descriptions are just strings/varchars.
          Its usually good practice to give some example data to help people understand your possibly confused narrative.
          The description table would normally hold the primary key and the FK would be on any table linking to the description table.

          You also don't say what the model is going to be used for(or if any changes to the design model are likely but we can ignore that for now)

          If all the entity has is a text description of the entity then a single table model is preferred. Anything more complex then separate tables for each.

          I agree whole heartedly to design as simple a model as the design constraints will allow for. I've seen some horrific examples of over complicated database designs (See my recent rant thread for a case in point)
          Coffee's for closers

          Comment


            #6
            Originally posted by minestrone View Post
            See, I would go for 1 everytime (If the entities are a small and never changing group).

            It's 1 table rather than 5, 1 class rather than 5. Simpler queries, faster access.

            (Just came across 2 in here and it got me thinking)
            I would go with a table for the entities, with a discriminator column to enable telling the types apart, plus an extension table for any subclass that warranted it, and a table for the descriptions with a single FK corresponding to the entity.

            The class model would have a base class with a subclass per type of entity.

            This is assuming the entities are related in some way as per your example.

            If it makes more sense to have a table per type of entity (i.e. not really related in any way) I would consider a single description table and use a composite foreign key of entity table + entity ID.
            While you're waiting, read the free novel we sent you. It's a Spanish story about a guy named 'Manual.'

            Comment


              #7
              Originally posted by minestrone View Post
              Just thought I would see what peoples thoughts are on this...

              *Thinks up example*

              lets say you had a database/model with with 4 different tables/entities...

              car boat plane train

              And you add 0 or many String/varchar descriptions to each of them, would you have...

              1. Description table/entity with 4 columns for the FKs and if it was for a car you would have the FK in a car column and the other 3 columns would be null.

              Or would you have

              2. 4 different tables/entitys CarDesc, BoatDesc etc.

              The descriptions are just strings/varchars.
              Neither.

              Code:
              CREATE table DescriptionType(
              DescriptionTypeID int not null PRIMARY KEY
              DescriptionEntity Varchar(50)--Car boat etc..
              )
              
              CREATE table Description(
              DescriptionID int not null 
              DescriptionTypeID int not null
              Description VARCHAR(100)
              )
              
              ALTER TABLE Decription ADD CONSTRAINT pk_primary_key PRIMARY KEY ON (DescriptionTypeID,DescriptionID)
              ALTER TABLE Decription ADD CONSTRAINT fk_foreign_key FOREIGN KEY ON (DescriptionTypeID) REFERENCES (DescriptionType.DescriptionTypeID)
              I'm sure there are syntax errors, but it gives you an idea as to what you need. You'll also have to assign a sequence or identity columns depending upon your DBMS.
              Last edited by rsingh; 27 August 2010, 11:13.

              Comment


                #8
                Surely there is only actually point in seperating the descriptions from the table rows if there is a many to one relationship? This may well be the case of course.

                Comment

                Working...
                X