Hello,
I would like to ask a suggestion because I just made a database that seems to be a bad structure. And i am worry about the future. :-\
One of my table already have 71 column. In my testing it can only handle i think 500. :
Is it a best practice to have many column in a table?
Please advise. Thanks.
That’s a hard question to answer without knowing what information you are storing.
When I want to school for database programming, we were told to do everything in 3rd normal form.
Here’s a PPT of the different normal forms.
Theirs many resources on the web, just search for 3rd normal form databases.
Hello TopCoder,
This 3NF you have me was the answer to my question. Thank you.
However, may I know why on the 3rd NF is a little confusing. Maybe I need enlightenment. Why did it have to separate the adviser and made another table ? :
I’m having trouble opening up that Powerpoint, but basically, in terms of a database…
When you normalize you store less data, and you don’t have gaps of empty columns.
Take a person for example
ID, First Name, Last Name, Date of Birth, Home Phone, Cell Phone, Work Phone, Fax Number
Not every person has a home, work, cell or fax number.
So to normalize it… you need 3 tables.
Members (ID, First Name, Last Name, Date of Birth)
Phone_Type (Phone_Type_ID, Description)
Members_Phone (Id, Phone_Type_ID, Number)
Phone_Type will have 4 rows
1, Home
2, Work
3, Cell
4, Fax
Members will have a lot of rows…
1, John, Doe, 1/1/1900
2, Jane, Doe, 1/1/1900
…
Members_Phone
1,1, 555-5555
1,2, 666-6666
2,1, 777-7777
…
This way you store the least amount of data possible, with no empty columns. It doesn’t make since to separate out Date of Birth, Last Name or First Name (because people usually only have 1 DOB, First Name and Last Name). But if you were going to capture (DOB, Wedding Date, Graduation Date, etc) then it will make sense to make a Special_Ocassion_Date_Types table and a Members_Special_Occassion Table.
So build a big table like you did with 71 columns, call it 1NF. Then start transforming it into 3NF.
Don’t worry about 4NF and 5NF (I hardly ever see that done in practice).
Then once you get table design down, you’ll work on adding in Foreign Keys to enforce integrity of your database. A lot of people will use an ER (Entity Relationship) Diagram tool to structure the database and make sure everything is connected visually and nothing is missed.
Hi Topcoder,
Good day.
It seems that my 71+ field table will break down in 2 tables
before set-up
Table1
RecNo - Autoincriment
Date
Time
Operator
protype
model
lotno
input
output
reject
STyield
ACyield
RejectType1
RejectType2
…
…
…
RejectType77
RejectType78
Now removing reject it will be
Table1
RecNo - Autoincriment
Date
Time
Operator
protype
model
lotno
input
output
reject
STyield
ACyield
Table2
RecNo
RejectType
Amount
Is this correct?
You will need a third table…
Table1
RecNo - Autoincriment
Date
Time
Operator
protype
model
lotno
input
output
reject
STyield
ACyield
Table2
RecNo
RejectTypeID
Amount
Table3
RejectTypeID
RejectTypeDescription
The table3 will hold the RejectTypeID and the RejectTypeDescription, then in table2 you will reference the rejectTypeID
After you get that done, you need to add “Referential integrity” to ensure your data remains clean and linked properly. Which is basically applying the foreign key relationships between table1, table2, and table3.
Once that is done. Then you have a good database design. Then you need to add in your proper indexing and set up your Primary keys, unique constraints, default values, etc.
Then you’re a database design wizard and you just need to apply this to anything you do in the future.
Also although you are allowed to have columns named data and time, you shouldn’t use those words for columns and will make coding your sql statements harder. You should change them to “rec_date” and “rec_time” or something.
Can I just jump in and say that’s the first time in industry I’ve seen anyone use normal forms before. We learnt to go to 5 NF in Uni “It’s standard, everyone in the industry goes to 5 NF” I’ve never once gone to 5, and find it extremely difficult to think of anything other than 3 NF.