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.

could you give some specifics?

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

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

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.

looks to me like you can have just one table, for animals

which way do you prefer?

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.

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

Thanks for the info.