It seems that you're using an outdated browser. Some things may not work as they should (or don't work at all).
We suggest you upgrade newer and better browser like: Chrome, Firefox, Internet Explorer or Opera

×
Community
General discussion (archive)(Non-trivial) MySQL question(27 posts)solved(27 posts)solved
(27 posts)solved
Pages:
1
2
This is my favourite topic
Posted January 03, 2012
avatar
AndrewC: That to me makes me think that your database isn't normalized. Things in the table_phone can have many to many relations to table_people and table_rooms, which is not good.
Currently it's one-to-one, null enabled. That would be perfect if it were only one table instead of two.
avatar
AndrewC: You honestly should differentiate the phone numbers for a room from the contact phone number associated with a person (which is the reason I guess you have a phone number assigned to a person and a room at the same time).
The number shouldn't be assigned to both, but in the given examples the possibility exists (which is why I want to find another idea on how to solve it). Both people and rooms may be added and removed (yes, magic house), and the numbers may be reassigned from a removed person to a new room or vice versa, thus there is no clear separation of "room phone number" and "person phone number".
avatar
Pidgeot: Correct, but the idea is that you use the *same* column for the ID, regardless of whether it belongs to a person or a room, and then just write in a *different field* if the ID refers to a person or a room. That means you get values like (person, 2) and (room, 4).
That's also an option. And guaranteed to work no less, only thing being it's not a foreign key, so the number might be assigned to a person or room that no longer exists. The work of checking and solving such cases would be assigned to the application.
Post edited January 03, 2012 by Miaghstir
Posted January 03, 2012
avatar
adambiser: (snipsnip)
I completely agree with Adambiser, if the database is on BCNF then by using a trigger / assertion or any kind of constraint would solve your problem (this is why these terminals exist).
Posted January 03, 2012
avatar
Pidgeot: Make at least one of these nullable if it's permitted to have an unassigned phone number, and add a unique index containing both of these fields if only one number can be assigned to a given person/room. MySQL (and AFAIK, the SQL standard as well) permits multiple rows with a NULL value, even if there's a unique index on the column.
avatar
Miaghstir: Wouldn't a unique index for two columns just make sure that both are not the same twice? Ie. if the columns are (4,5) in one row, they are allowed to be (4,6), (4,null), (5,5), and (null,5) in others?

If going this route, I would be looking for something that forces one of the columns to null when the other is not null (but both can be null, in case the phone number has no assignment).
avatar
Pidgeot: You can then add triggers to disassociate phone numbers when a row is deleted from table_person or table_room (assuming, of course, that this is ever necessary).
avatar
Miaghstir: Person being removed is one reason for a number to lose its assignment, yes.
Yeah, a unique index over two columns does not solve the problem. An index over multiple columns is just an index on the tuple of column values. So (NULL, 12 34 56 78) and (12 34 56 78, NULL) will not fail the uniqueness condition.

Triggers are drastic overkill. Don't implement a uniqueness constraint with triggers until you have exhausted ordinary means.

If you are willing to use a discriminant, as Pidgeot and Korell suggested in earlier posts, you can solve the problem without a non-portable UNIQUE constraint or any procedural superstructure. Discriminants are ugly, but generally a whole lot less ugly than triggers.
Posted January 03, 2012
avatar
cjrgreen: A question not yet answered that affects how you could implement this is, Does a "person" or "room" always have a phone number? (From another angle, should the "phone" column of these tables be NOT NULL)?
avatar
Miaghstir: Neither person nor room requires a number, it can be null in both cases.
I think cjrgreen has still nailed your problem. You are, as far as I can tell from your example, trying to model how the phones are owned by a room or a person. Therefore the person/room is not a property of the phone, the phone is a property of theirs. Each should have a phone property, if they don't have a phone then it's null (if you wish you could go through some stupid intermediary tables to avoid nulls, as some db enthusiasts will pretend is worthwhile).

That may not be the simplest for you to query, but that should always be a second thought.

EDIT: for 'Property' read field / column.

EDIT 2: OK, I see that you want to enforce some integrity on your model here, however that's not the right use of a database. What you're suggesting is to bring business logic into your data tier. A room shouldn't need to know about a person owning the same phone. That's an app logic problem.
Post edited January 03, 2012 by wpegg
Posted January 03, 2012
avatar
Pidgeot: Correct, but the idea is that you use the *same* column for the ID, regardless of whether it belongs to a person or a room, and then just write in a *different field* if the ID refers to a person or a room. That means you get values like (person, 2) and (room, 4).
avatar
Miaghstir: That's also an option. And guaranteed to work no less, only thing being it's not a foreign key, so the number might be assigned to a person or room that no longer exists. The work of checking and solving such cases would be assigned to the application.
That, of course, is where the triggers come in: by putting an ON DELETE trigger on table_person and table_room, you can have the database automatically issue a query like:

UPDATE table_phone SET assigned_type=NULL, assigned_id=NULL WHERE assigned_type=(type) AND assigned_id=OLD.id

whenever a row is deleted - that way, you don't have to do it "manually" in your application code.

You can also put an ON UPDATE trigger on table_phone to ensure that the row you're trying to associate the phone number to still exists (but such a scenario is probably very unlikely anyway, so odds are you don't *actually* need this).
Posted January 03, 2012
avatar
Miaghstir: That's also an option. And guaranteed to work no less, only thing being it's not a foreign key, so the number might be assigned to a person or room that no longer exists. The work of checking and solving such cases would be assigned to the application.
avatar
Pidgeot: That, of course, is where the triggers come in: by putting an ON DELETE trigger on table_person and table_room, you can have the database automatically issue a query like:

UPDATE table_phone SET assigned_type=NULL, assigned_id=NULL WHERE assigned_type=(type) AND assigned_id=OLD.id

whenever a row is deleted - that way, you don't have to do it "manually" in your application code.

You can also put an ON UPDATE trigger on table_phone to ensure that the row you're trying to associate the phone number to still exists (but such a scenario is probably very unlikely anyway, so odds are you don't *actually* need this).
If you would want to pursue some conjoined key with a trigger, why not just switch the primary keys of the other tables to guids, and have just one column called 'one or the other key'? It would be just as effective, and would avoid any specific naming of tables in the frankenstein key. Then your trigger could just do the equvalent of a FK lookup but instead check if they're in either table.
Post edited January 03, 2012 by wpegg
Posted January 03, 2012
avatar
wpegg: I think cjrgreen has still nailed your problem. You are, as far as I can tell from your example, trying to model how the phones are owned by a room or a person. Therefore the person/room is not a property of the phone, the phone is a property of theirs. Each should have a phone property, if they don't have a phone then it's null (if you wish you could go through some stupid intermediary tables to avoid nulls, as some db enthusiasts will pretend is worthwhile).

That may not be the simplest for you to query, but that should always be a second thought.
"Simple to query" isn't a problem, I want to avoid "unnecessary queries", and I somehow imagine that it's faster to do things in SQL when possible rather than "fetch data, handle data, send data back" in PHP (not that it actually matters much, the number of users shouldn't be able to put any measurable load on the servers anyway).
Posted January 03, 2012
avatar
Miaghstir: "Simple to query" isn't a problem, I want to avoid "unnecessary queries", and I somehow imagine that it's faster to do things in SQL when possible rather than "fetch data, handle data, send data back" in PHP (not that it actually matters much, the number of users shouldn't be able to put any measurable load on the servers anyway).
It's a risky path you're going down. It will be fine for something small (as your example sounds), but if you want to go into bigger databases, you need to avoid putting business level constraints in your data layer. The purist view of a data layer is that it is simply a model of how your data looks. This is constrained within itself (i.e. it is impossible to have a person key pointing at a person that doesn't exist). However it's really not intended to start enforcing higher level constraints like 'a phone can be in table a, or table b...' it becomes a mess.

Many people will disagree with this, I have the argument with other developers often about how much logic we should put in to data access. Generally it comes down to specific cases. However here it sounds like it's a validation issue, and when someone tries to change this, you find out if it's valid, if not, stop them. It's beyond integrity because you've opened the application of the phone information up to much.

As I just said, Guids (uniqueidentifier) would solve most of your problems, they allow you to have multiple keys in the same column without collision, but it always goes wrong from there on.
Posted January 03, 2012
avatar
wpegg: If you would want to pursue some conjoined key with a trigger, why not just switch the primary keys of the other tables to guids, and have just one column called 'one or the other key'? It would be just as effective, and would avoid any specific naming of tables in the frankenstein key. Then your trigger could just do the equvalent of a FK lookup but instead check if they're in either table.
UUIDs are not natively supported for storage by MySQL. The UUID function returns a string, and while you could certainly convert it to binary if you want to save space, you need to do that yourself. This will therefore be much less efficient.

For MySQL 5.1.20 and above, there is UUID_SHORT(), which return a 64-bit integer, and is likely to be just fine for the purpose, but I don't think other databases implement this function.
Posted January 03, 2012
You can try an EAV table, it might work depending on how the data is being used (see wikipedia for pros and cons on EAV tables).

This would remove NULL columns (more normalized) and would give you the ability to assign multiple phone numbers to "people" (ie. phones of type "home", "cell", "work" [I know you said you wouldn't need that, but still...]) and still enforce uniqueness.

You would remove any "phone_id" columns from your other tables and create a new table like this:

CREATE TABLE entity_phone (
entity_id INT,
entity_type ENUM( 'room', 'person' ),
phone_id INT
);

Add keys and constraints as necessary. You would need to join to this table to get phone numbers using the room id or person id and the type (ie. "SELECT p.area_code, p.phone_num FROM phone p INNER JOIN entity_phone ep ON p.phone_id = ep.phone_id WHERE ep.entity_id = :person_id AND ep.entity_type = 'person'").
Posted January 03, 2012
avatar
wpegg: The purist view of a data layer is that it is simply a model of how your data looks. This is constrained within itself (i.e. it is impossible to have a person key pointing at a person that doesn't exist). However it's really not intended to start enforcing higher level constraints like 'a phone can be in table a, or table b...' it becomes a mess.
Heh, this seems a lot like how I view CSS, HTML, and Javascript (separate the different layers), so I guess I just have to agree with you, if for nothing else than to stay true to myself.

cjrgreen's idea does look good though, I didn't even think of using views since I haven't used them before, so I'll have to play with them to see how they work.
Posted January 04, 2012
avatar
cjrgreen: CREATE VIEW phone_person_or_room AS
(SELECT phone FROM table_rooms)
UNION ALL
(SELECT phone FROM table_people);

CREATE UNIQUE INDEX i_phone_belongs_person_or_room ON
phone_person_or_room (phone);
A beautiful solution... shame MySQL (and MariaDB, by extension) doesn't allow indexes in views (yet).

Ah well, seems like controlling that no duplicates exist should be done in the application for now. And though the solution to put the room/person ID and a type definition in the phone number table, wpegg makes a convincing case of logic being best handled outside the data storage (and aligns with my idea that the room/person owns the number, not the other way around).

I'll keep my current solution for the database layer (id_phone as unique null-enabled foreign keys in table_people and table_rooms) and handle cross-table duplicate checks in code.
Post edited January 04, 2012 by Miaghstir
Pages:
1
2
This is my favourite topic
Community
General discussion (archive)(Non-trivial) MySQL question(27 posts)solved(27 posts)solved
(27 posts)solved