Jump to content

MySQL Generalization/Specialization


jc2006

Recommended Posts

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. 
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites


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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.