Creating tables for database

[TechBoyJK]

Hi Guys,

I'm working on a simple aircraft classified ad application. I'm designing the main tables for the classified ads. The initial app was written with everything in one big table, but I'm trying to simplify it down into several smaller tables. (normalization)

-Core (main data)
-categories
-details
-features

Core is the main table. This includes the essential information (title, id of member who listed ad) and is going to be the core table to be included in search results. Basically, when someone is browsing listings, the core table includes the data that is included for the ad in each listing.

Categories is self explanatory. This table includes the categories that are bound to the ad.

The difference between details and features is that the details table will include data that requires custom entry about specifics (mileage, serial number) and info that can't be answered with a yes/no, where as features is a list of standard features found on aircraft that each listing may or may not have (air conditioning, etc).

First question is, would it be best to include the primary key id of the master table (core table) and include that in every sub-table, or include the id's of the subtables in the master table. I'm thinking it would be best to include the record id's of the subtables as columns in the master table. That way when you hit the master table, you are given the exact id of the records of all other related tables. (this would provide for a precise "seek" in the subtables searches for related records). The other way, you would have to search the subtables for records that match the master record ID every time. You couldn't just seek.

Granted, I'm still working on adding features/columns, so I'm not asking for advice on what data I'm collecting about aircraft. Just advice on the general structure and how I'm seperating the data.

tbl_aircraft_classifieds_core

classified_id (int-identity-pk)
member_id (int-fk)
category_id (int-fk)
details_id (int-fk)
features_id (int-fk)
title (nvarchar-45)
shortline_a (nvarchar-25)
shortline_b (nvarchar-25)
shortline_c (nvarchar-25-null)
quantity (int)
currency (varchar-30)
price (decimal-9/2-null)
year (smallint-null)
make (nvarchar-25)
model (nvarchar-35-null)
show_thumbnail (bit)
thumbnail_url (varchar-255-null)
address_a (int)
db_list (bit)
created (datetime)
created_ip (varchar-16)
last_modified (datetime-null)
last_modified_ip (varchar-16-null)
status (varchar-15)


tbl_aircraft_classifieds_categories

category_id (int-identity-pk)
category_parent_a (varchar-35)
category_child_a (varchar-35)
category_parent_b (varchar-35-null)
category_child_b (varchar-35-null)
category_parent_c (varchar-35-null)
category_child_c (varchar-35-null)


tbl_aircraft_classifieds_details

details_id (int-identity-pk)
condition (varchar-20)
market_value (decimal-9/2-null)
warranty (bit)
registration (nvarchar-50-null)
serial_number (nvarchar-50-null)
total_time (nvarchar-35-null)
overhaul (nvarchar-35-null)
flight_rules (varchar-30-null)
inspection_status (varchar-30-null)
airframe (varchar-30-null)
props (smallint-null)
avionics_radios (nvarchar-50-null)
color (varchar-30-null)
seats (varchar-8-null)
doors (varchar-30-null)
engine (nvarchar-25-null)


tbl_aircraft_classifieds_features

features_id (int-identity-pk)
air_conditioning (bit)
am_fm_stereo (bit)
cassette (bit)
single_cd (bit)
multi_cd (bit)
premium_sound (bit)
dvd (bit)
leather (bit)
power_seat (bit)
dual_power_seats (bit)

 

[Markbnj]

Typically the master table id would be included in the related tables, and those columns would be brought in with a join. The problem with doing it the other way around is that the master table has to change every time a new dependent table is added.

 

[WannaFly]

Originally posted by: Markbnj
Typically the master table id would be included in the related tables, and those columns would be brought in with a join. The problem with doing it the other way around is that the master table has to change every time a new dependent table is added.

Agreed - but don't forget indexing. It's much more efficient to index the related tables when they have an ID pointing to the master. The other way around indexing would be far less efficient, I believe.
Edit: I think this doesn't apply to the OP, it only applies in a one-to-many or many-to-many relationship. I may still be wrong though

TechBoy: I know it's a little off topic, but it looks as if there really isn't a reason to separate details and features into a separate table. Assuming every core row will have 1 and only 1 details and features row, why not just put those columns in core? Unless there is somewhere else in the application using them separately or there are multiple ads using the same details and features rows.

 

[TechBoyJK]

Originally posted by: Markbnj
Typically the master table id would be included in the related tables, and those columns would be brought in with a join. The problem with doing it the other way around is that the master table has to change every time a new dependent table is added.

OK,

So even though it would be quicker performance to just include the related table id's into the master table (avoids a search for related tables) you are saying it's not a good idea "only" if adding new related tables is a problem? That makes sense, but I'm trying to way the performance gain I think we would get by keeping the related table id's in the master table vs the other way.

The only thing is that I don't see adding another column to hold a new table id much of a big deal vs the performance gain of keeping the id's in the master table.

My only other thought is the possibility of orphaned records. If the related tables themselves don't contain the data needed to link it to the primary table, if the primary table is deleted, there would be no way to know what record the related table belonged too. Granted, if the primary table is deleted, then the other records could be deleted. So even if they were left in an orphaned state (master table deleted, related tables not deleted), we could simply delete all related records that are 'orphaned' because if they don't have a master table to relate to, it means there's no main record for it, and it's a useless record.

Make sense? not trying to be argumentative.. just trying to decide the best path.

 

[troytime]

i used to prefix all of my table names with tbl_ as well!!

and then one day when i was looking at a list of 200+ tables i realized that there wasn't really any benefit to it.

 

[Crusty]

Originally posted by: TechBoyJK

Originally posted by: Markbnj
Typically the master table id would be included in the related tables, and those columns would be brought in with a join. The problem with doing it the other way around is that the master table has to change every time a new dependent table is added.

OK,

So even though it would be quicker performance to just include the related table id's into the master table (avoids a search for related tables) you are saying it's not a good idea "only" if adding new related tables is a problem? That makes sense, but I'm trying to way the performance gain I think we would get by keeping the related table id's in the master table vs the other way.

The only thing is that I don't see adding another column to hold a new table id much of a big deal vs the performance gain of keeping the id's in the master table.

My only other thought is the possibility of orphaned records. If the related tables themselves don't contain the data needed to link it to the primary table, if the primary table is deleted, there would be no way to know what record the related table belonged too. Granted, if the primary table is deleted, then the other records could be deleted. So even if they were left in an orphaned state (master table deleted, related tables not deleted), we could simply delete all related records that are 'orphaned' because if they don't have a master table to relate to, it means there's no main record for it, and it's a useless record.

Make sense? not trying to be argumentative.. just trying to decide the best path.

Not sure what RDBMS you're using, but with mysql and the InnoDB storage engine you can specify actions to take on parent/child deletion/updates like cascade the delete or null the parent_id in the child row.

 

[TechBoyJK]

Originally posted by: Crusty

Originally posted by: TechBoyJK

Originally posted by: Markbnj
Typically the master table id would be included in the related tables, and those columns would be brought in with a join. The problem with doing it the other way around is that the master table has to change every time a new dependent table is added.

OK,

So even though it would be quicker performance to just include the related table id's into the master table (avoids a search for related tables) you are saying it's not a good idea "only" if adding new related tables is a problem? That makes sense, but I'm trying to way the performance gain I think we would get by keeping the related table id's in the master table vs the other way.

The only thing is that I don't see adding another column to hold a new table id much of a big deal vs the performance gain of keeping the id's in the master table.

My only other thought is the possibility of orphaned records. If the related tables themselves don't contain the data needed to link it to the primary table, if the primary table is deleted, there would be no way to know what record the related table belonged too. Granted, if the primary table is deleted, then the other records could be deleted. So even if they were left in an orphaned state (master table deleted, related tables not deleted), we could simply delete all related records that are 'orphaned' because if they don't have a master table to relate to, it means there's no main record for it, and it's a useless record.

Make sense? not trying to be argumentative.. just trying to decide the best path.

Not sure what RDBMS you're using, but with mysql and the InnoDB storage engine you can specify actions to take on parent/child deletion/updates like cascade the delete or null the parent_id in the child row.

Oh yea, definately. I'm just focusing on the core design. stored procedures and triggers can all be put in place afterwords.

 

[Crusty]

They are part of the table definitions. You create foreign keys on the tables with relations to each other and set the flags for cascade etc at design time. It's very much part of the core design.

 

[GodlessAstronomer]

Not really related to your original question, but your category table raises big red flags for me:

tbl_aircraft_classifieds_categories

category_id (int-identity-pk)
category_parent_a (varchar-35)
category_child_a (varchar-35)
category_parent_b (varchar-35-null)
category_child_b (varchar-35-null)
category_parent_c (varchar-35-null)
category_child_c (varchar-35-null)

You should have a categories table that looks something like
category_id (int-identity-pk)
parent_category(int-fk) [references categories]
category_name(varchar-35)

And an intermediate table like tbl_aircraft_in_categories
category_id(int-fk) [references categories]
aircraft_if(int-fk) [references aircraft_classifieds]

This allows for more than 3 categories and no limits on category hierarchy. It also avoids wasting disk space with a bunch of NULLs.

 

[Ka0t1x]

Originally posted by: TechBoyJK
First question is, would it be best to include the primary key id of the master table (core table) and include that in every sub-table, or include the id's of the subtables in the master table. I'm thinking it would be best to include the record id's of the subtables as columns in the master table. That way when you hit the master table, you are given the exact id of the records of all other related tables. (this would provide for a precise "seek" in the subtables searches for related records). The other way, you would have to search the subtables for records that match the master record ID every time. You couldn't just seek.

This may be incorrect depending on what DB engine you're using, but from my experience and working with MySQL and ORACLE... Always just repeat your core ID# in the other tables as a FK and join based on Core ID, this way you can create ONE->Many relationships very easily, as well as deal with null data from other tables (outer join), doing all of that would be very hard with how you have it currently setup.

And actually from looking at the structure of the tables more the only reason to have a separate table would be to do a two table join with a meets table to the categories.. and the other data could probably be in a single table.

 

[GeekDrew]

Originally posted by: Ka0t1x
And actually from looking at the structure of the tables more the only reason to have a separate table would be to do a two table join with a meets table to the categories.. and the other data could probably be in a single table.

Unless there's a possibility that "features" might be added/removed... in which case I'd personally create a "features code table". Something like:

features:
feature_id (int, identity, pk)
feature_label (varchar)

classified_features:
classified_feature_id (int, identity, pk)
classified_id (int)
feature_id (int)

 

[KIAman]

You want to create an ERD to help build your schema.

It all depends on the relationships between the different objects.

From your OP you got basically 4 objects (table names).

1. Aircraft
2. Categories
3. Details
4. Features

Each object has its associated attributes (field names).

Then you need to map out the relationships between each ones.

Does aircraft have one category? Two? Can it have 0?
Can a detail have a feature?

Your OP is an example if everything is a many_or_none to 1 relationship (an aircraft has exactly 1 category, 1 detail and 1 feature but that category but each category can map to many or no aircraft, each detail can map to any or no aircraft and each category can map to any or no aircraft).

Your OP so far describes the following relationship in English words.
1. An aircraft has a Category
2. An aircraft has a Detail
3. An aircraft has a Feature

Once you work your relationship out, you can directly map your ERD directly to relational tables.

Maybe your really want the following relationship.

1. An aircraft has multiple Categories
2. An aircraft has a Detail
3. An aircraft has none or multiple Features

When it comes to mapping out "none" relationship, you can set the FK field to allow null. When you want to map out multiple relationship between 2 entities, you have to create another object that describes the relationship. For example #1 above would need Object Aircraft, Object Categories and Object AircraftWithCategories.