LinkBack URL
About LinkBacksThat's exactly what i was thinking beforehand but not sure if it was good to implement it. I'll make the changes nowOriginally Posted by Az
That's exactly what i was thinking beforehand but not sure if it was good to implement it. I'll make the changes now
I would have a table called type or somthing similar, and assign a type_id to logs/blogs/articles. Then you could use one table for logs/blogs/articles and have the field type_id describe which type it is.
To err is human. To really foul things up ... you need a computer.
What he said ^^^
Plus:
- If you're going to have a userid - not just the username - as the key for the user table then why carry the username onto the other tables as a foreign key? Similarly if you want a userid (for space reasons for example) then make sure you create a unique index on the username so that people can't create duplicates.
- If you're going to allow people to edit items you might want to add a couple of timestamps to the item table (or the sep' tables if you want to keep them that way). Something like 'created_at', 'last_modified' to have at least some basic tracking in there and make the current 'date' field just a nominal entry - i.e. if someone @ hexus wrote up a review and uploaded it the week before it was due to go live (e.g. for NDA reasons) they would want to show the published date of the article as the NDA date and not the date is was added to the system... Which also means:
- You need to decide whether or not to further catagorise the items / their states i.e. sep' status field AND sep type field OR expand your items types to include (for example) 'live article' and 'test article' (har har)... This is the beauty of having an item type and a table to resolve those codes into descriptions / categories - cuz then you can add more as needed without creating separate tables. You should also consider allowing users to add their own categories for the same reason.
If you find at some point that your blog / article type items require different fields (unlikely) and so would really really need sep tables consider using a global item table still and putting further details into another (e.g. child) table... Think of it as a messy type of inheritance / abstraction (presume you're familiar with OO concepts)
Last edited by malfunction; 01-02-2005 at 08:20 PM.
That's exactly what i was thinking beforehand but not sure if it was good to implement it. I'll make the changes now
It's fine as long as the item type is a part of the key / separately indexed
Which brings me onto another point related to actually coding the CMS / the CMS 'reader' to display content in the system... Repeat after me.
"explain" is my friend...
"explain" is my friend...
"explain" is my friend...
If you want something that scales well you need to make sure you have appropriate indexes (indices if you must) on your tables - it's almost as important as writing the correct queries in the first place
There's the updated version of it, i included the last_modified field just as malfunction said, it'd be handy for the logs.
Just a note, this is a very specific CMS, i.e. only one user is going to use it although i could add users in the future. Plus, this is a starting point, i'll make the mistakes now and fix them later learning from my mistakes (as you do).
Is that diagram ok so far? Point out any changes needed.
Not a lot to add really now that you've put it in context. I'd still ask you why you've put an id field in the user table but use the name field as a foreign key in the items table. Either use username as the primary key (and accept a relatively large key on that table and as a foreign key wherever it's used) or use the userid as the foreign key (but still make sure the name field on the users table is unique...
Other than that a note on table and field names... Which I'm sure everyone has an opinion on (and like where you put the braces in c/c++/java it doesn't matter all that much too):
Especially for primary keys (such as 'id') I always include the table name - simply because if you don't you always have to qualify it (use 'table.id') in selects over multiple tables... Can also be useful for common fields such as dates / timestamps. And foreign keys (despite the fact that it contradicts what I just said) always have exactly the same name as the table they originate from (cuz it means you never forget what they are called). And personally (probably from my OO experience) I don't use plurals in table names. Couldn't quite tell you why - it just feels wrong to me. And even though it is as obvious as can be in your case I would always denote the key fields and fields that you think will need an index on them when drawing up a structure... And note what field type you want for each...
Anyway to sum up I would do it like this:
table: item
item_id (int, pk)
title (varchar)
date (date - needs index?)
last_modified (datetime - aka timestamp in oracle IIRC)
content (blob)
user_id (int, fk)
item_type_id (int, fk)
(I've put "needs index?" on the date field as you may find yourself wanting to select either all articles in a given date range or all articles in a given date range for a specific user or item type... In which case you should consider adding an index on date alone or (more likely) on user and date and another on item type and date... Adding too many indexes slows down updates / inserts so try to only create ones that are needed... Though in this case - as a largely read biased system you won't need to be too careful... Again I say that explain is your friend... As is "show create table" to see which indexes are actually on the table - useful if you are (for example) developing at home but hosted elsewhere as I've often forgotten to implement tweaks made at home on the live servers...)
table: user
user_id (int, pk)
name (varchar, unique)
email (varchar)
full_name
(I would probably change name to username too - just being anal though which is why I didn't change it above)
table: item_type
item_id (int, pk)
item_type (varchar)
I would also consider something like this:
table: item_type_group
item_group_id (int, pk)
item_id (int, pk)
description (varchar)
So that you could using a hexus type site as an example again, have an item_group_id for 'reviews' and item types of 'Graphics card', 'CPU', 'Motherboard' (i.e. sections and subsections) - which would also be useful for searching the site - on which point you should also consider if you are going to allow anyone to search the CMS based upon item content - title (not too bad but not good for a varchar or wild card search) or the actual content (not quick at all for blob fields)... A quicker solution is a meta tag type solution - lots of keywords associated with the content in question (but that's OTT and whether or not that would add anything to a system that's properly categorised or where searches are infrequent and users are patient is something to think about)
Last edited by malfunction; 01-02-2005 at 09:01 PM.
For the user table shall i just get rid of user_id then?
Simple answer is that I would in this case as we're only talking about (1) somewhere to start and (2) a database where the field is only used as a foreign key once (per record, on the item table) and that the item table will have rather large records anyway (content being a blob and all). So in this case it's not bad at all.
However it can be bad if, for example, you needed a long username - i.e. 64 unicode characters (128 bytes) and have to reference the key (username) in lots of places (used it as a foreign key) where something like a 32-bit integer (4 bytes) would be lot less storage space and hence quicker to read / write (particuarly important for index updates / searches). Even though this sounds contrived it could happen because people want a long username that can be easily remembered so even though the full range of values will never be used the keylength still needs to be long to be practical...
Just to contradict myself though - if you prefer to keep the id on the user table just use that as the foreign key not the username (either method is good - just don't mix the two).
What about that?
Well... That's almost exactly what I did in nice diagram form (btw - what are you using to create the fancy diagrams?)... So I would like it wouldn't I?
What about that?
You've even forgotten to give full_name a field type... I would suggest a varchar... varchars need length too (e.g. varchar(64))... and you copied "item_id" for the key on the item_type table (whereas it should obviously be item_type_id)...
That best be it
And i'm using photoshop to do the funky stuff
Nope... item_type_id as foreign key in the item table... and item_type_id as the primary key for the item_type table... On which note it might be time to down tools for the day - you might get alternative / better advice from someone else tomorrow so let the dust settle a bit before you start coding
That best be it
And i'm using photoshop to do the funky stuff
so ditch item_id from item_type table? I'm confused :S
I know
Change the name of the primary key on the item_type table to item_type_id... Then reflect this change for the foreign key in the item table...
So:
table: item_type
item_type_id (int, pk)
item_type (varchar)
And:
table: item
item_id (int, pk)
title (varchar)
date (date)
last_modified (datetime)
content (blob)
user_id (int, fk)
item_type_id (int, fk)
He means replace the item_id with item_type_id, so it includes the table name in the PK. How are you going to handle the log-in then? Username/password? Wouldn't you want a password field? Unless you hard code it in PHP, as you said it's just for you.
...
We posted at the same time
To err is human. To really foul things up ... you need a computer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote