Adding an Organization schema to existing person/relationship schema

database-designschema

I'm not a database programmer. I really don't plan on becoming one; I dabble. My skills and work lie elsewhere, and these keep me busy enough. Nevertheless, I find myself needing, and begging, for some quick help at times in an area in which I have no time or need to learn fluently. I have officially attempted some trials at building a couple of new tables for this, but to no avail. I do most of my learning in a hands-on setting, and will know how this particular schema works once I've received some direction (which is all I'm asking for, I think) and implemented it.

That said, the following was suggested by @RolandoMySQLDBA four years ago, and I've found it terrific for a project I'm building.

I'm hoping he will see this and that he, or someone, can help me extend this to include a table of "Organizations" along with a table of Organization rel_types, to be associated with any of the persons…

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE relationship
(
    rel_id INT NOT NULL AUTO_INCREMENT,
    person_id1 INT NOT NULL,
    person_id2 INT NOT NULL,
    reltype_id TINYINT,
    PRIMARY KEY (rel_id),
    UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2),
    UNIQUE KEY inner_affinity (reltype_id,person_id2,person_id1),
    KEY has_relationship_to (person1_id,reltype_id),
    KEY has_relationship_by (person2_id,reltype_id)
);
CREATE TABLE relation
(
    reltype_id TINYINT NOT NULL AUTO_INCREMENT,
    rel_name VARCHAR(20),
    PRIMARY KEY (reltype_id),
    UNIQUE KEY (rel_name)
);
INSERT INTO relation (relation_name) VALUES
('friend'),('follower'),('foe'),
('forgotabout'),('forsaken'),('fixed');

I will be, and am, grateful to anyone who can assist me with this.

Best Answer

If you want to keep things simple, it may be enough to add organization(organization_id PK, ...) table and person_organization_relationship (person_org_relationship_id (PK) , person_id,organization_id ,reltype_id , date_from, date_thru). Depends on requirements you may or may not add unique constraint, on , for instance, (person_id, organization_id,reltype_id). I would reuse the same relation table which is used for describing relationships between persons. In many cases such model will be working just fine.

If more general model is needed, concept of Party can be added. Party can be a person, or organization, or something else that interacts with the system . For instance if RDBMS supports enums, you can have

PARTY (party_id PK, party_type enum('person','organization'), [common attributes]);

Then you either add 2 tables, PERSON and ORGANIZATION (which both have party_id as a primary key and foreign key to PARTY) and store their attributes separately, or store all attributes in PARTY table.

Concept of Party lets you store any type of relationship between person and organization,or 2 organizations , or 2 persons in one table. Moving forward, it'll make other things easier to implement. For example, various contact information such as address(physical or electronic), phone, etc can stored in one place . On the other hands, it has its price. The more universal model you are implementing, the more time you'll need to spend in the beginning.

So I'd recommend to use simplest model that satisfies requirements .

Some notes regarding current physical implementation of relationship table:

     UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2), 
     UNIQUE KEY inner_affinity (reltype_id,person_id2,person_id1),
     KEY has_relationship_to (person1_id,reltype_id),
     KEY has_relationship_by (person2_id,reltype_id)

It's better not to have the least selective column (reltype_id) as a leading column in outer_affinity and inner_affinity
For enforcing uniqueness it's enough to have one of outer_affinity, inner_affinity
I'd start with just two indexes UNIQUE KEY UQ_relationship_person_1_person_2 (person_id1, person_id2, reltype_id) and INDEX IDX_relationship_person_2(person_id2) (depends on typical queries it might be useful to person_id1 as second column to this index).
The table seems to miss date_from (and maybe date_thru) attribute .

Related Solutions

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

Sql-server – Schema for 1 <-> 0..1(+), many related but only one primary

Try a filtered unique index, not a trigger.

CREATE UNIQUE INDEX ixOnePrimaryEach ON dbo.Document(PersonID)
WHERE IsPrimary = 1;

Best Answer

Related Solutions

Mysql – Drop auto increment hack w/o alter table

Sql-server – Schema for 1 <-> 0..1(+), many related but only one primary

Related Question