MySQL Generalization/Specialization

[jc2006]

Hi,

I am new to MySQL.  I was wondering if using generalization/specialization hierachies are recommended for MyISAM tables in MySQL.  I understand that there is no recursion in MySQL which would be nice to have for parent/child relationships.  Also, since I am using MyISAM tables, concurrency would at times involve locking a parent and some of its children (locking many tables vs. locking a single table when hierarchies are not used). Would it be better to not use hierarchies?

Thanks.  Any help is very much appreciated. 

[fenway]

You can use hierachies all you want, you just have to handle traversing them differently.

[jc2006]

Thanks for the response.  What about the locking of parent and children tables?  Because parent AND children tables can be locked during certain concurrency situations, wouldn't it be more efficient to avoid hierarchies (so that only one table will be locked)? 

Thanks

[fenway]

Well, depending on how you store the data, you can have it all in a single table to begin with.  And I'm not sure what you're referring to by locking in the MyISAM situation -- these are non-blocking in the general case.  Please clarify.

[jc2006]

Hi

MyISAM involves table locking as opposed to row locking for InnoDB.

I was speaking of an instance such as this:

TABLE books --- Parent table
    bookID
    bookName
    bookDescription

Table automotiveBooks --- Child Table
    typeOfCar
    carCompanyName
    dealershipsNamed

Table childrenBooks  --- ChildTable
    ageGroup
    cartoonCharactersUsed

If an update was needed to be made to an automotive book by a user 1 in a session (where a read is first done to get the records and then an update is performed) to change the bookName, bookDescription, typeOfCar, carCompanyName, and dealershipsNamed, then both the parent table "books" and the child table "auotomotiveBooks" would have to be locked.  Because the parent table would be locked, a user 2 in another session would not be able to perform the same read/update operation for the childrenBooks table and the books parent table until the parent table is unlocked by the user 1 in the prior mentioned session. 

This would be in lieu of having all the data in one table.  In this situation, only one table would be locked:

Table automotiveBooks
  bookID
  bookName
  bookDescription
  typeOfCar
  carCompanyName
  dealershipsNamed

I am new to database design so I hope that I am making sense...

Thanks for helping.

[fenway]

You are correct... if you want to get around concurrency issues in MyISAM, you'll need to LOCK the tables, at least briefly.  I'm not sure I understand your design, though....

[jc2006]

First, I wanted to thank you for being so helpful. I really appreciate your efforts - thanks!

I have an example here from a book I have called "Database Modeling and Design"

In this example "Individual" is a parent to "Employee" and "Customer"

So:

create table individual (
indiv_id char(10),
indiv_name char(20)
indiv_addr(20)
primary key(indiv_id))

create table employee(
emp_id char(10),
job_title char(15),
primary key(emp_id),
foreign key (emp_id) references individual on delete cascade on update cascade);

create table customer(
cust_no char(10),
cust_credit char(12),
primary key(cust_no),
foreign key (cust_no) references individual on delete cascade on update cascade);

In my example I have two users who log onto the site...

"individual" is a generalization of "employee" and "customer."  If a user who logs on w/ a given session were to update attributes in both "individual" and "employee", for example, both "individual" and "employee" tables would have to be locked.  This means in another user session, that "customer" table would momentarily be locked from the "individual" parent table.  Wouldn't this slow things down for a site w/ many users?

In these instances, wouldn't it be better to have two tables "employee" and "customer?"  Both of these tables would have their aforementioned attributes and keys plus each of them would have the parent's attritbutes so:

create table employee (
emp_id char(10)
emp_name char(20)
emp_addr char(20)
job_title char(15),
primary key(emp_id));

create table customer (
cust_no char(10),
cust_name char(20),
cust_addr char(20),
cust_credit char(12),
primary key (cust_no)); 

Is this a valid argument for repeating attributes in tables as opposed to generalization?

Thanks again.

[fenway]

There are a few things here; first, I assume that emp_id => individual(id) and cust_no => individual(id).. this wasn't explicit in your FK constraints.  Second, while it is true that there would be locking issues, this would only matter on UPDATE, which presumably happens much less than read (which is why MyISAM tables are often used).  Third, in your last example, why not have a single table with a flag?

[jc2006]

Hi

In the example I would be hesitant to make a single table with flags because employee.job_title and customer.cust_credit are unique attributes to each employee and customer tables.

Could you please elaborate on your comment about fk constraints?  It's actually a book example, and in practice, I would not actually use foreign keys because they are not allowed with MyISAM tables...but I know the notation would be similar.

Thanks.

[fenway]

Ok... but you would still keep the common elements in a single table, and then horizontally split out the ones that are unique to each type.