Please read up on Data Normalization, General Indexing concepts, and Foreign Key constraints to keep data clean with referential integrity. This will get you going. Storing data in arrays may seem natural to you on paper, but to the db engine the performance with mostly be without index use. Moreover, you will find on Day … Read more
You have at least these five options for modeling the type hierarchy you describe: Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types. This means a lot of columns, most of which are NULL on any given row. Class Table Inheritance: one table for Products, … Read more
@Bill Karwin describes three inheritance models in his SQL Antipatterns book, when proposing solutions to the SQL Entity-Attribute-Value antipattern. This is a brief overview: Single Table Inheritance (aka Table Per Hierarchy Inheritance): Using a single table as in your first option is probably the simplest design. As you mentioned, many attributes that are subtype-specific will … Read more
In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems: Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5 Can’t use foreign key constraints to link values to a lookup … Read more
may be this will help you: CREATE TABLE COUNTRY( country VARCHAR(255) PRIMARY KEY, language VARCHAR(255), timezone TIMESTAMP, currency FLOAT ) CREATE TABLE REGION( region VARCHAR(255) PRIMARY KEY, landtype VARCHAR(255), country VARCHAR(255) FOREIGN KEY REFERENCES COUNTRY(country), scenery VARCHAR(255), page INTEGER UNIQUE, ) CREATE TABLE RESORT( resort VARCHAR(255) PRIMARY KEY, region VARCHAR(255) FOREIGN KEY REFERENCES REGION(region), transfertime … Read more
You should read the conventions. This should give you the understanding on how to name your database and models correctly, ready for use with CakePHP.