Hey guys im new to databases so take it easy please! But basically its an important aspect of the website i am developing so its something i have to learn. A summary of what im looking for from the database, (although i know i have alot to learn) the database must be able to:
allow users to register/login
provide the user their own space
allow users to upload pictures and also delete pictures
allow users to comment and also thumbs up other users pictures
allow users to post threads
allow users to reply to these threads
I know its alot how much will be solved by creating a database, and can anyone point me in the right direction please?
I started by listing on paper each pice of data I needed to store. then I grouped them which meant that user details like user_name, pwd, displayed name etc were in one table. address in another. a table for photos which uses the usernames foreign key, to connect/relate them. you’ll also need a set of tables, maybe two maybe three, for storing a thread title, then the thread content and then the thread responses, where each table has a value from another, to relate the records.
wow thanks for your reply its really really helpful in getting me started ie the listing of what i need for example. But no I have never built anything yet but hope to make this my first project, any tutorials or extra help will be much appreciated, i know im not going to pick this up in a day or 2
1: Identify Entities (User, Picture, Comment, etc) [These will be your main tables]
2: Identify Properties of the Entities (User’s Name, Email, etc) [These will be fields in your tables]
3: Identify Relationships Between Entities (A Comment is posted by a User) [These will be Foreign Keys and/or Join Tables]
Or more wordily: What are you storing information about, what information are you storing about them, and what ties do they have to each other.
brilliant like r937 said, brilliantly simplified and summarised, not to mention thank you IBazz for your info aswell!! can you provide me a visual on how these would look, say for example something common, say the entity is User, the properties of the entity being first name, second name, password, email address, info, so these would be in a row of 6 right?
but let me get it right, these would refer back to the database (MySQL) which holds the actual information right?
If an explanation can be beautiful, that one surely is. makes me wobble though. i’m going to go through all of my 200 tables and see if I actually did it like that.
I may be some time !!
create table users
( id int not null auto_increment primary key
, first_name varchar(99) not null
, last_name varchar(99) not null
, password varchar(99)
, email_address varchar(99)
) engine = innodb default charset=utf8 collate=utf8_unicode_ci;
‘99’ is to be set to a number so that it is big enough to allow the actual amount of characters that may ever be in that column (sometimes called field)
the default charset can be whatever you need it to be so look up mySQL charsets
and collate can be as you require as well, so look up MySQL collate or MySQL collations.
now;
if you have an address table for storing addresses (postal, delivery, office etc), you would have a table something like this:
create table addresses
( id int not null auto_increment primary key
, address_1 varchar(99) not null
, address_2 varchar(99)
, zip varchar(24)
, country varchar (99) not null
) engine =innodb default charset=utf8 collate=utf8_unicode_ci;
and then you could have a joining table which would relate the user to their addresses, potentially a one-to-many relationship because we can have more than one address.
create table user_addresses
( id int not null auto_increment
, address_id int not null
, user_id int not null
, address_type varchar(24) not null
, primary key ( address_id, user_id, address_type)
, index reverse_ix ( user_id, address_id, address_type)
, constraint userAddresses_addresses_fk
foreign key (address_id)
references addresses(id)
, constraint userAddresses_users_fk
foreign key (user_id)
references users(id)
) engine=innodb default charset=utf8 collate=utf8_unicode_ci;
the last table shows the IDs of ‘addresses’ and ‘users’ tables as foreign keys so your queries can join the tbales and get the data from both as related. And that last table also clarifies which address you may be looking for so, if you query wants the delivery address for a user, it asks for the address for the user where address-type = ‘delivery’ or whichever.