CodeIgniter Forums
Table Structure - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Table Structure (/thread-36132.html)

Pages: 1 2


Table Structure - El Forum - 11-22-2010

[eluser]ganjasensation00[/eluser]
I just want to know some tips in creating good table structures for large development.


Table Structure - El Forum - 11-22-2010

[eluser]Elias de A. Rodrigues[/eluser]
One the most important things to me when i'm developing a large database is the field names because some developers insist in use a understandable names in their fields. Then when you go to make same manteinance you gonna be most lost then a blind guy in a crossfire.

Other tip's is organize well your fields, relationships, create indexes only in the fields the you will really use otherwise that is not gonna be a good thing.


Table Structure - El Forum - 11-22-2010

[eluser]ganjasensation00[/eluser]
In creating primary keys? Which do you prefer, numbers or chars, and which is faster? Is this also be considered in creating table structures?


Table Structure - El Forum - 11-22-2010

[eluser]InsiteFX[/eluser]
This is a nice tool for MySQL Databases.

MySQL Workbench

InsiteFX


Table Structure - El Forum - 11-22-2010

[eluser]Elias de A. Rodrigues[/eluser]
[quote author="ganjasensation00" date="1290503450"]In creating primary keys? Which do you prefer, numbers or chars, and which is faster? Is this also be considered in creating table structures?[/quote]

this depends the what data do you want to keep in this tables but generally i prefer using int to primary keys like ID.and set to autoincrement too. but if you want to use an email field you can to but then the table never will accept more then 1 register with the same email address.

Then To Primary Keys generally use integer because is most fast but you can use chars too but is most slow.


Table Structure - El Forum - 11-22-2010

[eluser]InsiteFX[/eluser]
Use integers for primary index keys.

Create an index on email address or a unique key on email address.

InsiteFX


Table Structure - El Forum - 11-23-2010

[eluser]Bartolo![/eluser]
Can add this question to this topic?

Which naming method (example) do you prefer? Or maybe different?

example One:
(without prefix)

Code:
users
--------
id(pk)
name
email


messages
--------
id(pk)  
message
date
users.id(fk)

example Two:
(with prefix)

Code:
users
--------
user_id(pk)
user_name
user_email


messages
--------
message_id(pk)
message_message
message_date
users_id(fk)



Table Structure - El Forum - 11-23-2010

[eluser]Narkboy[/eluser]
Generally, I try to start field names with the table name.

Basically - it prevents problems when you're joining several fields with 'id'. It also stops a load of hassle debugging.

You can end up with very long field names, which can be an issue - especially since I name my tables in a related way:

product
product_audit
product_update
product_update_result

Poor example, but you get the idea.

I think the worst name I've managed to create is:
ct_product_update_result.result_image_outcome

Which is an error field for importing product images from a remote server. You suffer a little from typing extra when writing queries etc, but for my money it's still easier than spending hours to discover that you've ordered things wrong because 'id' != 'id' != 'id'.

Smile

***Edit***

By all means use short forms as long as they are understandable:

product => prod
message => msg
user => usr

Always stick to the same convention.

Also, try to make sure that the field name is meaningful - message_message is not as good as message_body or message_content. msg_body or msg_content are equally as good

Avoid 'date' - date of what? Use 'date_created', 'date_updated', 'date_submitted' - it's not unusual for me to have 2 or more dates in a table.


Table Structure - El Forum - 11-23-2010

[eluser]Bartolo![/eluser]
Quote:ct_product_update_result.result_image_outcome
That's not so bad ;-P

But i completely agree with you. Since i had to search for a bug for days and than discovered that i had duplicate field names (after joining) i always try to create fieldnames like that. More is less (work).


Table Structure - El Forum - 11-23-2010

[eluser]smilie[/eluser]
Well, also MySQL can handle itself unique field names, so you do not have to do:

SELECT a.id, b.id, c.id from table_a as a, table_b as b, table_c as c....

Rather:

select a_id, b_id, c_id from a,b,c...

I also always have unique ID of every table (auto increment + index). Helps a lot for edit / save / delete.

Cheers,
Smilie