DB/OO Design question

[minestrone]

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.

 

[HairyArsedBloke]

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.

 

[lilelvis2000]

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.

 

[minestrone]

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)

 

[Spacecadet]

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)

 

[doodab]

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.

 

[rsingh]

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.

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.

 

[ASB]

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.