For the last few months I've been designing a blogging engine and a website that'll use it. I know that there are countless pre-made ones out there, but I've decided to create my own mainly due to educational reasons. Before this, I've had extremely little knowledge of databases before. I have the basic layout setup and I'm just asking for any advice I can get on it. As you can tell, it's not meant to be a feature packed blog and is just meant to be simple and easy to use. Here's the current tables. Names are just place holders. Blog Entires Posted Time & Date DATETIME - Primary Key Content TEXT Status ENUM (Normal, Static, Draft) Allow Comments BOOLEAN Subject TINYTEXT Categories Name TINYTEXT - Primary Key Linked Categories Entry Primary Key - Foreign Key to Blog.Entires.Posted Time & Date Category Primary Key - Foreign Key to Categories.Name Comments Posted Time & Data DATETIME Content TEXT Approved BOOLEAN Name TINYTEXT IPAddress INT EntryForeign Key to Categories.Posted Time & Date There are still a few questions I have about database design. Should all tables contain a primary key? The above is all I need and I've been told that you should never add unnecessary fields. Should I add any index keys anywhere? I was planning to have Comments.Approved and Blog Entires.Status as them (as similar to WordPress's design) due all queries processed will use these fields, but I was advised that something so small wouldn't make any difference. Huge thanks to any replies. desu is offline Reply With Quote Multi-Quote This Message Quick reply to this message View Public Profile Send a private message to desu
Its good practise to create a primary key on all your tables. - With tables like you 'Linked Categories' it isnt really needed for relations but will be a much great help when updating or deleting a record. - I wouldnt advise making a date type your primary key. Just make it an int and autoincrement or use some other factor to create a unique key (like md5 of some random number id for example) - An index on a small data set wont make much difference. Also an index on a field with minimal variations wont make much difference. Over indexing can even slow queries down in some cases