Populating a database

[NIA2]

Hi everyone,

I've just created the structure of a database and am deciding the best way
to insert the data. The database includes a number of tables that have
foreign keys referencing primary keys in other tables. The application I'm
building will make use of the data in one of the following ways (haven't
decided yet):

1) The data will be used in a Flash movie and will have an accompanying CMS
associated with it, or
2) The data will be used in a Flash movie but won't have a CMS

If I decide on a CMS, should I build this first and use the PHP and MySQL to
create an interface whereby I can enter the data and have the foreign keys
entered in the correct place automatically?

Or if I don't go with a CMS and just need to populate the database for use
in Flash, what's the most efficient way of doing this? I mean I'm aware I
can populate it with a .csv file but this wouldn't take care of inserting
the correct foreign key values against the appropriate records. I wouldn't
have to manually enter these foreign keys, would I?

So does the way you populate a database depend on whether you're using a CMS or not? Have I got the right idea?

Any help much appreciated.

 

[elsenorjose]

A key (primary or foreign) is a unique identifier that tells the DBMS that a record in a table is unique. If your records are correctly identified, then your table structure takes care of the PK and FK already. So, regardless of HOW you import the data, there should already be a key defined. Typically this is an ID of sorts. In your example, it could be a movie ID or a combination of movie name and play time, if that's unique. What is it exactly that you are trying to do?

 

[NIA2]

Thanks for the reply,

I've pasted in the sql code that represents the structure of the database. Since the garment_id is used as a foreign key in the garment_to_colour, garment_to_size and garment_to_category lookup tables, I'm not sure how I'd populate these columns.

Can you help?

CREATE TABLE `garments` (
`garment_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier` varchar(30) NOT NULL,
`garment_type` varchar(30) NOT NULL,
`title` varchar(60) NOT NULL,
`code` varchar(20) NOT NULL,
`description` varchar(400) NOT NULL,
`extra_info` varchar(50) default NULL,
`image` enum('y','n') NOT NULL,
`swatch_image` enum('y','n') NOT NULL,
PRIMARY KEY (`garment_id`),
UNIQUE KEY `supplier` (`supplier`,`garment_type`, `description`, `title`,`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `colours` (
`colour_id` smallint(5) unsigned NOT NULL auto_increment,
`colour` varchar(20) NOT NULL,
PRIMARY KEY (`colour_id`),
UNIQUE KEY `colour` (`colour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `garment_to_colour` (
`garment_id` smallint(5) unsigned NOT NULL,
`colour_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`colour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `sizes` (
`size_id` smallint(5) unsigned NOT NULL auto_increment,
`size` varchar(15) NOT NULL,
PRIMARY KEY (`size_id`),
UNIQUE KEY `size` (`size`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `garment_to_size` (
`garment_id` smallint(5) unsigned NOT NULL,
`size_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `categories` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `garment_to_category` (
`garment_id` smallint(5) unsigned NOT NULL,
`cat_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;