Posted 24 July 2006 - 09:45 PM
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.
Posted 25 July 2006 - 12:55 PM
Posted 25 July 2006 - 03:33 PM
Posted 25 July 2006 - 05:11 PM
MyISAM involves table locking as opposed to row locking for InnoDB.
I was speaking of an instance such as this:
TABLE books --- Parent table
Table automotiveBooks --- Child Table
Table childrenBooks --- ChildTable
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:
I am new to database design so I hope that I am making sense...
Thanks for helping.
Posted 25 July 2006 - 06:52 PM
Posted 25 July 2006 - 11:13 PM
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"
create table individual (
create table employee(
foreign key (emp_id) references individual on delete cascade on update cascade);
create table customer(
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 (
create table customer (
primary key (cust_no));
Is this a valid argument for repeating attributes in tables as opposed to generalization?
Posted 26 July 2006 - 01:37 AM
Posted 26 July 2006 - 05:29 PM
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.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users