Table design issue ...

[dbeezzz]

So I'm putting to get some tables from form input and from session data. It's kind of going like this

LETTER_TABLE:

LETTER_ID         LETTER_NAME  LETTER_TEXT   USER_ID 
(auto_increment)                              (KEY)

... so I have around six tables which are all based around that theme. The specific letter_id increment and then the key being the user_id.
... I usually access them by their user_id

INSERT INTO LETTER_TABLE (letter_name, letter_text)
VALUES ( letter_name, letter_text, user_id )

... Is it bad design to have all of my tables accessed by a user_id key ? I don't really see anyone else doing it that way ?
Also, I might want to make a blog table with more than one entry per user_id. Any suggestions on what I can do here ? I think maybe I'm not thinking about the overall design issues here ?

 

[spookie]

You can actually make a seperate table for relationship between user and letter (and other entities).
something like

LETTER_TABLE:

LETTER_ID(Auto Increment, PK)         LETTER_NAME  LETTER_TEXT   

USERvsLETTER TABLE:

USER_ID      LETTER_ID

I don't know if you have a one to one relationship between USER and LETTER. If you have you can make USER_ID as PK in USERvsLETTER TABLE.




--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[rac2]

To clarify the idea of keys. A table may have one primary key, the values are unique, a given value appears in only one row. Perhaps you have a table of Users; then user_id would be the primary key for that table.

Other tables may refer to the user with the user_id. This is a foreign key. Your table LETTER_TABLE has a foreign key, user_id. Foreign key values may appear in many rows in a table. The primary key for LETTER_TABLE is LETTER_ID; every row in LETTER_TABLE has a different value of LETTER_ID.

Any of the tables which will be linked to the Users table may have user_id as a foreign key.

The typical reason for defining an association table as suggested by spookie is to represent a many-to-many relationship between tables. For example, a letter may be authored by two or three or four or more users; and each user may author many crank letters. There would be a row in the USERvsLETTER table for each combiniation of user and letter. This combination of user_id and letter_id would be unique, no two rows will have the same combination of values. Together the two columns are the primary key for the USERvsLETTER table. Each column by itself is considered a foreign key.

These things are quite common aspects of database design; in fact they are essential to the relational database idea.

HTH.