Data model

[GoogleCodeExporter]

:::::::::::::::::::::::::::::
::CHANGES IN THE DATA MODEL::
:::::::::::::::::::::::::::::

* WARNING
- I don't understand the syntax you are using to create the name of the
fields. Taking a look in the data model I should know what fields are
"foreign keys" (and of what table). Another important thing is to know the
"data type". ie. we can use at the beginning of the fields that preffix:

 * varchar, mediumtext, bigtext, with malloc : "s_"
 * char, works different with the memory: "c_"
 * integer, long: "i_"
 * boolean [ int(1) ] : "b_"
 * decimal, float: "f_"
 * datetime: "dt_"
 * date: "d_"
 * timestamp: "ts_"

- field names, table names and index names: always lower case and
descriptive names

- ie: the definition of "category" (the name "t_category") but the name of
the fields:
 * i_id
 * s_name
 * s_description
 * fk_i_id_category (fk_<referred_field_name>_<what_table>)

MORE FIELDS IN THE TABLES AND REESTRUCTURATION...
::ad:: -> the name "t_ad"
id -> ok, the name i_id
pub_date -> ok, the name dt_date
update_date -> needed, the name dt_update_date
category_id -> ok, the name fk_i_id_category
price -> ok, the name f_price
title -> ok, the name s_title
description -> ok, the name s_description
location_id -> no, I don't understand this field
num_views -> no, we need more tables with statistics... fase 2 -> we can
study if is needed that field desnormalized in that table
fk_i_id_t_countries -> the country. Foreign key of t_countries.
s_country -> I'm not sure if we need that field desnormalized.
fk_i_id_t_regions -> the region. Foreign key of t_regions (default value 0)
s_region -> the name desnormalized of the region (important for performance)
fk_i_id_t_cities -> the city. Foreign key of t_cities (default value 0)
s_city -> the name desnormalized of the city (important for performance and
just in case we don't have in our table the inserted city)
s_city_area -> the name of the neighbourhood (is impossible to do a table
with all the city areas in the world)

:LOCATION: -> deprecated :P I don't understand your idea of have the
location of the item in another table because all the items must be
geolocalizated.

:t_cities:, :t_regions:, :t_countries: -> needed. Fill those tables using
geonames.org

:ad_comment: (t_ad_comment)
id -> ok. i_id
dt_date -> needed
ad_id -> ok. fk_i_id_t_ad
author_name -> ok. s_author_name
author_email -> ok. s_author_email
body -> ok. s_body
b_active -> needed. b_active can be a int(1). The default should be 1 but
you can deactivate form the backoffice. At the backoffice you can select if
you want email validation by user or not.
b_spam -> needed. b_spam can be a int(1). The default value should be 0 but
you can mark it at the backoffice (or a plugin like akismet).

:ad_resource: -> what is that?

:user: -> we need 2 tables. First for us (backoffice) and the other for the
web users

:preference, page, and module: -> I cannot define them very well now. I
need to speak with you.

Original issue reported on code.google.com by danielgs on 18 Jan 2010 at 5:05

[GoogleCodeExporter]

in t_ad:
s_address

Original comment by danielgs on 18 Jan 2010 at 5:52

[GoogleCodeExporter]

Important! new database model: start to work with that data model

Original comment by danielgs on 20 Jan 2010 at 4:05

• Added labels: Priority-Critical
• Removed labels: Priority-Medium

Attachments:

• model_osclass.pdf
• model_osclass_GOOD.mwb

[GoogleCodeExporter]

Resource: Width and height are not compatible with the concept of the table. 
What are
the dimensions of a PDF document? And what the dimensions of a sound?
Category: If we put the language on this table then we will have category 
"Cars" with
differents id of "Cotxe" and "Coche". Is this ok?
Category: Isn't better to split the table in category and category_name or
category_description? (with the translations for each locale)
It makes sense to have a dedicate content-type table? What is the idea for the
comment field on this table? (What about translations?)
Why do we want to know population on city and region?

Original comment by santiago...@gmail.com on 24 Jan 2010 at 4:51

• Changed state: Started

[GoogleCodeExporter]

Resource: the most common resource for classifieds are images and video, we 
need the
aspect ratio if we don't want to calculate (in PHP) it each time. For PDF, 
sound and
other resources, that field can be NULL.

Category: No, the id of "Coche" and "Cotxe" is the same, the primary key is 
"i_id and
language". It is possible I forgot the primary key in the "data model". 

Category_Name: Just for us but I don't know if it is necessary

The content-type table is because we have finite content-types. If not, we are
repeating each time for example "image/gif". You decide, this field is only a 
suggest.

Original comment by danielgs on 24 Jan 2010 at 7:02

[GoogleCodeExporter]

Maybe category_description is valid to some classifieds to put a comment next 
to the
name.

Original comment by danielgs on 25 Jan 2010 at 9:52

[GoogleCodeExporter]

- Added b_spam (marked or not as spam), b_user_validation (validated by the 
user),
b_active (validation by the website administrator) in oc_t_item and 
oc_t_item_comment.

- New tables: oc_t_tmp_item_description (copy of the table t_item_description 
table:
to have drafts) and oc_t_tmp_item (copy of the table t_item without the boolean
status fields -not necessary-, and with a new session field (char(40), using 
SHA1 in
PHPSESSIONS). The not null and default values are well defined in the "data 
model"
attached.

Original comment by danielgs on 25 Jan 2010 at 12:13

Attachments:

• model_osclass_GOOD.mwb

[GoogleCodeExporter]

Original comment by santiago...@gmail.com on 23 Mar 2010 at 6:45

• Changed state: Fixed