Results 1 to 9 of 9

Thread: Table Design

  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: Table Design

    Hi,

    I've got several tables that have common data in them. I'm coming from an object orientated programming background so i've made a "parent" table with the common fields and then setup a relationship between it and the "child" tables. I'm thinking this may not be the optimal design for database's as your always going to need to get two tables when you interact with the data. Can anyone give any feedback on this issue as from a design point of view it's cleaner to have them separated up but that's the only reason i can give to do it that way.

    Thanks,
    Tim.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you give some specifics?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    5
    sure

    table : coins_transaction

    fields :
    id
    created
    coins

    table : coins_tranactions_membership

    fields :
    coins_transaction_id
    membership_type_id

    table : coins_tranactions_purchase

    fields :

    coins_transaction_id
    item_id

    So i'll never access "coins_transactions" directly but there are multiple other tables that also have a foreign key to coins_tranactions to represent different types of transactions.

    Thanks,
    Tim

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm afraid i don't see the issue

    possibly because i don't understand your coins_transactions table

    every transaction has a coins column, but what the heck is that? how is "coins" a transaction?

    perhaps you could illustrate the issue by showing what the single-table version of this schema would look like
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2011
    Posts
    5
    Ok i think the table names i've used have been misleading for you.

    Table : Animal
    Fields : Id, Name, NumOfLegs

    Table : Dog
    Fields : Id, AnimalId, IsCarnivore

    Table : Rabbit
    Fields : Id, AnimalId, HasFloppyEars

    So we've got three tables. In object orientated programming you'd have a parent class "Animal" and then two sub class's "Dog" and "Rabbit". Should i in my database schema also have three tables as above or should i have two tables like this

    Table : Dog
    Fields : Id, Name, NumOfLegs, IsCarnivore

    Table : Rabbit
    Fields : Id, Name, NumOfLegs, HasFloppyEars

    Thanks,
    Tim.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks to me like you can have just one table, for animals

    which way do you prefer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2011
    Posts
    5
    For sure in the case of the animals your right it would be better off in a single table however this isn't a real world example as in i was just showing where i was coming from in my thinking.

    So the original question was if it's not possible to combine all into one table then am is there a performance penalty on having the separated out tables in mysql or is it so insignificant that i can do what i've done at the moment and separate them out so as to have a cleaner design.

    Thanks,
    Tim.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whether you use the supertype/subtype model is determined by the requirements of the actual situation

    i can't help you if you post stuff that "isn't a real world example"

    if you want more info, search on supertype/subtype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2011
    Posts
    5
    Thanks for the info.

Tags for this Thread

Posting Permissions

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