250 GTE Register

Moderators: 330GT, abrent

Enzo250GTO
Posts: 53
Joined: Sat Nov 30, 2002 9:05 am
Location: San Francisco, CA
Contact:

Post by Enzo250GTO »

OK before everyone goes off on their own path making these if we have one common schema we can share that out. Here is an example of a very simple schema for MySQL (found below). Now I'm willing to type all this crap out by hand and share it out to everyone if everyone agrees to use it. Of course I want everyone to also agree to what information is in it. When I can get this I can begin making this (so a little over 110 tables) schema as a register.sql file that anyone can use to make all 100+ tables in a matter of seconds in their database. I can write the schema for MySQL, Oracle, MS SQL, and PostgreSQL. Then we can work together as Rob said. He will have the same db as I do and he can work on 328s when he is finished he can send it to me, while he was doing that I was working on say testarossa and I send that to him, and person X was working on 250 GT and sends us his info to add into the database. The key is to keep the structure the same so the info plugs right in. Like I said I can even use XML so no human ever has to update the "master" db.

CREATE TABLE ferrari_models (
model varchar(25) NOT NULL default '',
year int(11) NOT NULL default '0',
made int(11) default NULL,
etype varchar(8) NOT NULL default '',
esize int(11) default NULL,
bore varchar(12) default NULL,
edescrip varchar(100) default NULL,
power int(5) default NULL,
torque int(5) default NULL,
trans varchar(100) default NULL,
chassis varchar(100) default NULL,
suspension varchar(100) default NULL,
rsuspen varchar(100) default NULL,
KEY modelid (id)
) TYPE=MyISAM;
User avatar
tyang
Posts: 4060
Joined: Wed Nov 27, 2002 10:28 pm
Location: New York
Contact:

Post by tyang »

Hello all your Database specialists!

Not knowing much about Databases, is there a way to transfer one database set to another one even if the fields don't match? Eventually, you'll have to agree with ONE system, but right now, I see several sets of registries that need to be merged.

Tom
Rob328gts
Posts: 10
Joined: Tue Oct 29, 2002 3:36 pm
Contact:

Post by Rob328gts »

Ha, I told everyone I wasn't a DBA. Good model 330! I think that's a very good design and takes in account things like contacts that I didn't even consider.
Rob328gts
Posts: 10
Joined: Tue Oct 29, 2002 3:36 pm
Contact:

Post by Rob328gts »

I don't see a need for 100+ tables Andrew, so please explain more. I think 330's schema is the best I've seen so far.

It really doesn't matter too much if our schemas match, down the road if we want to merge then that will make it easier. However, merging two differnt schemas is simple as writing a script to say which field correlates to which field. The hard part is just looking at both schemas and mapping that overlap, then you're usually left with many fields that one has, but not the other.
Enzo250GTO
Posts: 53
Joined: Sat Nov 30, 2002 9:05 am
Location: San Francisco, CA
Contact:

Post by Enzo250GTO »

Rob,
Your correct. I wasn't thinking. I'm working on a schema right now in MySQL, with about 6 tables. I will post it as soon as I finish.
User avatar
Yale
Posts: 825
Joined: Mon Sep 02, 2002 4:56 pm
Location: New York City

Post by Yale »

Just to weigh in with my 2 cents. After I started to look for my car I realized how many great stories each of these cars has so I started a database of four headlight 330's as a way of learning about these cars histories. After Kerry started his site and after Tom gave me a printed copy of the GTE register, I realized how much work this would be to make it consistant and accurate (and complete). So now, after I collected info on about 2-300 cars, I just send info to Kerry and grab any Four Headlight stories I come across. It would be great if your registry could have room for narrative, such as it may be.

As far as registry models, I think the www.330register.com is a very beautiful site. Paul who owns it only lets out some of the info he has collected though, which is a shame. I would think in this internet society the world will pass him by on that front.

Yale
Enzo250GTO
Posts: 53
Joined: Sat Nov 30, 2002 9:05 am
Location: San Francisco, CA
Contact:

Post by Enzo250GTO »

He is a schema which I have been working on with others via e-mail. I will try to post a picture of this as well to give you an idea visually.

#
# Ferrari Register - MySQL schema
#

#
# Table structure for table 'tbl_car'
#
CREATE TABLE car (
vin mediumint(8) DEFAULT '0' NOT NULL,
car_type_id mediumint(8) DEFAULT '0' NOT NULL,
interior_id mediumint(8) DEFAULT '0' NOT NULL,
exterior_id mediumint(8) DEFAULT '0' NOT NULL,
engine_num mediumint(8) DEFAULT '0' NOT NULL,
rlhd tinyint(1) DEFAULT '0' NOT NULL,
manufacture_date mediumint(8) DEFAULT '0' NOT NULL,
intial_price float(10,2) DEFAULT '0' NOT NULL,
PRIMARY KEY (vin),
KEY interior_id (interior_id),
KEY exterior_id (exterior_id),
KEY car_type_id (car_type_id)
);


#
# Table structure for table 'tbl_interior_color'
#
CREATE TABLE interior (
interior_id mediumint(8) DEFAULT '0' NOT NULL,
leather tinyint(1) DEFAULT '0' NOT NULL,
cloth tinyint(1) DEFAULT '0' NOT NULL,
velour tinyint(1) DEFAULT '0' NOT NULL,
fabric_colour varchar(50) NULL,
date int(12) DEFAULT '0' NOT NULL,
KEY interior_id (interior_id)
);

#
# Table structure for table 'tbl_exterior_color'
#
CREATE TABLE exterior (
exterior_id mediumint(8) DEFAULT '0' NOT NULL,
metalic tinyint(1) DEFAULT '0' NOT NULL,
red_colour tinyint(1) DEFAULT '0' NOT NULL,
yellow_colour tinyint(1) DEFAULT '0' NOT NULL,
blue_colour tinyint(1) DEFAULT '0' NOT NULL,
white_colour tinyint(1) DEFAULT '0' NOT NULL,
black_colour tinyint(1) DEFAULT '0' NOT NULL,
silver_colour tinyint(1) DEFAULT '0' NOT NULL,
colour_name varchar(30) NULL,
date int(12) DEFAULT '0' NOT NULL,
KEY exterior_id (exterior_id)
);

#
# Table structure for table 'tbl_car_type'
#
CREATE TABLE car_type (
car_type_id mediumint(8) DEFAULT '0' NOT NULL,
model varchar(30) DEFAULT '0' NOT NULL,
manufactured_from mediumint(8) NOT NULL,
manufactuerd_to mediumint(8) NOT NULL,
number_made mediumint(8),
KEY car_type_id (car_type_id)
);


# --------------------------------------------------------
#
# Table structure for table 'tbl_history'
#
CREATE TABLE history (
vin mediumint(8) NOT NULL,
history_id mediumint(8) UNSIGNED NOT NULL auto_increment,
owner_id mediumint(8) NOT NULL,
start_date mediumint(8) NOT NULL,
end_date mediumint(8) NOT NULL,
history_info text NULL,
PRIMARY KEY (history_id),
KEY owner_id (owner_id)
);


# --------------------------------------------------------
#
# Table structure for table 'tbl_owner'
#
CREATE TABLE owner (
owner_id mediumint(8) NOT NULL,
firstname varchar(20) NULL,
sirname varchar(30) NULL,
city varchar(30) NULL,
country varchar(30) NULL,
email varchar(40) NULL,
phone varchar(14) NULL,
email_private tinyint(1),
phone_private tinyint(1),
KEY owner_id (owner_id)
);
Enzo250GTO
Posts: 53
Joined: Sat Nov 30, 2002 9:05 am
Location: San Francisco, CA
Contact:

Post by Enzo250GTO »

Here is a picture of the above but its not as extensive as above (there is more info in the owners table, and color tables that is not showing here).

http://www.ferrariforum.net/images/schema.jpg
User avatar
Yale
Posts: 825
Joined: Mon Sep 02, 2002 4:56 pm
Location: New York City

Post by Yale »

Well there's no room for any narrative there. Also how about a space for whicch number car it is in a series for instance,
1st 330GT
2nd 330GT

etc. ?

Yale
Enzo250GTO
Posts: 53
Joined: Sat Nov 30, 2002 9:05 am
Location: San Francisco, CA
Contact:

Post by Enzo250GTO »

[quote="Yale"]Well there's no room for any narrative there. Also how about a space for whicch number car it is in a series for instance,
1st 330GT
2nd 330GT

etc. ?

Yale[/quote]

That would be in the 'tbl_model' and each would it gets own entry. So when someone trys to add there car to the register they do not type it in but they choose their model from a dropdown menu. The menu would be exhaustive containing all models and their variations. So in your example 2nd 330GT would get say the model_id of xxx, when the person enters their vin it goes in the vin field in the 'car' table and when they choose 330GT 2nd xxx will go in the 'model_id' field in the 'car' table. So when you look up vin xxxxxx it will see that it is model_id xxx which is 330GT 2nd edition and will then display all the info on that car as well as the owner and history.
User avatar
Yale
Posts: 825
Joined: Mon Sep 02, 2002 4:56 pm
Location: New York City

Post by Yale »

Not what I meant. My thoughts was first car, second car, third car - not speaking about Series/Version numbers but instead the FIRST 330GT made, the SECOND 330GT made, the THIRD 330GT made. Compré.

And no room for some notes on history and condition, lets say a paragraph on each car?


Best, Yale
Enzo250GTO
Posts: 53
Joined: Sat Nov 30, 2002 9:05 am
Location: San Francisco, CA
Contact:

Post by Enzo250GTO »

[quote="Yale"]Not what I meant. My thoughts was first car, second car, third car - not speaking about Series/Version numbers but instead the FIRST 330GT made, the SECOND 330GT made, the THIRD 330GT made. Compré.

And no room for some notes on history and condition, lets say a paragraph on each car?


Best, Yale[/quote]

Yale,
Its all there. The vin is stored in one table with other various IDs which are look up IDs. They correspond to information in the other tables. Wouldn't the vin s/n tell you which was first second etc. It is not shown here but there is a field in the models table which has the first and last vin for a model (its sn range). The history is in the history table. Like I said the picture table is not the most upto date. Here is the history information:

CREATE TABLE history (
vin mediumint(8) NOT NULL,
history_id mediumint(8) UNSIGNED NOT NULL auto_increment,
owner_id mediumint(8) NOT NULL,
start_date mediumint(8) NOT NULL,
end_date mediumint(8) NOT NULL,
history_info text NULL,

This records the exchange of the car from one buyer to the next. So start_date is when X person owned and history_info would contain what happened to it during his ownership. Here is an example of one car:
sn owner_id sdate edate info
1234 4 xx-xx xx-xx info during this period
1234 56 xx-xx xx-xx info during owner 56 period
Enzo250GTO
Posts: 53
Joined: Sat Nov 30, 2002 9:05 am
Location: San Francisco, CA
Contact:

exhaustive list

Post by Enzo250GTO »

OK here is what I hope is an exhaustive list of Ferraris. I'm going to use this as part of the Register. www.ferrariforum.net/models/year/ Please let me know if you see an omited model. I know it stops at 2000. I'm working on that as well. This is very long and tuff like everyone said it would be but I'm getting there.
Post Reply