Question Regarding Naming Fields in A MySql Database (Normalization)

[jimmyoneshot]

I unserstand the use of normalization and how it can be essential but in my database I have two tables that are not connected or related to each other but are in the same database, a 'links' table and a 'createduserlinks' table but most of the fields in the tables share the same names. What I mean is this:-

 

LINKS TABLE

linkid (Primary Key)

categoryid (Foreign Key)

label

icon

url

 

CREATEDUSERLINKS TABLE

linkid (Primary Key)

userid (Foreign Key)

label

icon

url

 

Although this works does it breach 3rd normal form and would it be better to change my createduserlinks table perhaps to something like this:-

 

CREATEDUSERLINKS TABLE

createdlinkid (Primary Key)

userid (Foreign Key)

crealtedlinklabel

createdlinkicon

createdlinkurl

 

Basically I'm wondering what is best practice? 

[ignace]

I think it would be best to keep the links table and add a boolean field called user_contributed

[jimmyoneshot]

Cheers. Sorry I think I've explained it incorrectly the client will add links into the links table and all of the users will be able to view these links. But the created userlinks table will contain all links created by users of the system, these will not have a category hence there being no categoryid in the createduserlinks table just the userid to show what user each link has been created by. This means the categoryid would be inappropriate to created user links so the 2 tables couldn't be combined. You see the links section of my site is completely seperate from the section in which users can create their own links. I'm just wondering about the practice of it really. Is it bad practice to have fields named the same as other fields in other tables?

[Mchl]

It is not a bad practice. In fact if you keep names the same, it might come in handy in many situations (you might for example create a function that will take a table name as parameter and return a list of links form either table)

[ajlisowski]

What would be the harm in combining the tables, having category be 0 if a user submitted it, and have userid be 0 if it was not user submitted?

 

Does that break normalization? They wouldnt be null values, just 0.

[jimmyoneshot]

Yes on second thought I'm starting to see how that would be useful as it eliminates the need for any such repeating values and makes the extra createduserlinks table redundant. Furthermore I could probably simply leave the categoryid field value blank for any links that are user submitted and likewise leave the userid blank for any links that aren't. It's just a pain that I now have to update my php files to all insert and retrieve data to and from the links table rather than both tables. DOH!

 

Thanks for your advice too Mchl. Now I know that it's not bad practice to have fields in seperate tables named the same. It's just that when reading normalization I see so much about repeating values etc not being allowed so this got me thinking does this also apply to field names or just the values within fields?

[Mchl]

Rule of a thumb: Are you likely to ever need to fetch both kinds of links in one query? If not or very rarely, they probably should belong to different tables.

[jimmyoneshot]

While I'm on this subject I also allow my users to add comments to links in the links table so I have a users table and a comments table but they both contain the field 'username' is this bad practice in regards to normalization/db design trends/whatever you call it? i.e:-

 

USERS TABLE

userid (Primary Key)

username

password

 

COMMENTS TABLE

commentid (Primary Key)

linkid (Foreign Key)

userid (Foreign Key)

username (can't be a foreign key because it's not a primary key in another table but it is foreign)

commenttext

 

It is the username being in the comments table that is confusing me. You see I need that to be there as when I populate the online table which displays all my comments for each selected in my site each comment also needs to display the username of the person that posted it so this obviously also needs to be in the comments table but I'm not sure as to whether THIS is bad practice considering that it would also be in the users table?

[jimmyoneshot]

Rule of a thumb: Are you likely to ever need to fetch both kinds of links in one query? If not or very rarely, they probably should belong to different tables.

 

Nope I have 2 seperate php files each containing a query both of which only link to one of the tables so I suppose ditching the createduserlinks table is the best way to go and having both queries directed at the one universal table.

[Mchl]

To display a username in comments list you would simply JOIN both tables.

SELECT u.username, c.commenttext FROM users AS u CROSS JOIN comments AS c USING(userid) WHERE linkid = 1

[jimmyoneshot]

Ah right. Cheers mchl. So if I ditched the username column from the comments table how would I work that into my current query which is this considering I need to select ALL the commetns data from the comments table as well as ALL the usernames of the comment posters from the users table:-

 

SELECT * FROM comments WHERE linkid = '$param_linkid' ORDER BY commentid

 

Cheers for your help. I'm really bad at creating complex queries

 

[Mchl]

You should be able to work it out by yourself now

 

SELECT u.username, c.* FROM users AS u CROSS JOIN comments AS c USING(userid) WHERE c.linkid = '$param_linkid' ORDER BY c.commentid

[jimmyoneshot]

That looks like it'd work. Thanks a lot mate