I think my suggestion was a bit hasty and you know data modeling can
only be done when you know the meaning and purpose of the concepts.
I think you want to represent an entity user.
For that entity create a table USER with a unique nonchanging id and
whatever attributes you want to store for a user (any user)
Then you want to store the information of user's friendships with each
other, right?
That is a many-to-many relationship between user and and another user.
Further the relationship is symmetric (well at least one would hope for
mutual feelings).
So I would come up with a new entity/concept/substantive FRIENDSHIP.
It would have columns
- FRIENDSHIP_ID (primary key, every table needs an identity)
- USER1 references USER table
- USER2 references USER table
Now you can for example define that a user can not be friend of himself.
CHECK(USER1 <> USER2)
You can define that do not store one friendship more than once
UNIQUE(USER1, USER2)
I don't think it is possible to disallow USER2 -> USER1 if you already
have USER1 -> USER2 ... bummer
Anyway you can be assured that all friendships stored are between
EXISTING users (no nullpointer exceptions).
- rami
On 17.6.2011 11:50, [email protected] wrote:
Hi Rami,
Thank you very much for the answer, but I still don't understand much
about the two tables, which you recommended.
For example:
My original database:
-----------------------------------
userId | Friends
------------------------------------
user1 | user3
user2 | user3
user3 | user1, user2
-------------------------------------
The new tables are:
Userdata:
----------
userId
-----------
user1
user2
user3
--------
Friends:
----------------------
friendId | userId
------------------------
user1 | user3
user2 | user3
user3 | user1
user3 | user2
---------------------------
1)In this Example the user1 and user2 has the same friend user3, so
the "Friends" Table needs more space then the first ARRAY Type. Isn't it?
2) Why do I need the table "Userdata" ? I can easily use "select *
from Friends where userId = ???" and get all the information. Will
the Table userdata speed up the selection?
May be my questions are very stupid... I am really new in database. :(
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/kNJ1EmbkyW0J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
