DBSCHEMA for 2257 Database - GoFuckYourself.com

mikesouth · 06-02-2005, 05:05 PM

I think its pretty complete...questions/suggestions are welcome

Performer Table
puid INTEGER Primary Key
Performer_Id BLOB (id type jgp png tif gif etc)
Performer_Fname VARCHAR (First Name)
Performer_Lname VARCHAR (Last Name)
Performer_Mname VARCHAR (Middle Name)
Peformer_DOB DATE (Birth date)
Performer_addy1 VARCHAR (address line 1)
Performer_addy2 VARCHAR (address line 2)
Performer_City VARCHAR (city)
Performer_State CHAR(2) (state)
Performer_zip VARCHAR (Zip code)
Performer_email VARCHAR (email addy)
Performer_phone VARCHAR (phone number)

Aliases Table
puid INTEGER
alias VARCHAR puid+alias = Primary Key

Shoots Table
Shootid INTEGER Primary Key
ShootDate DATE (Date of Shoot)
Location VARCHAR (Shoot Location)
Video CHAR(1) (Video Y/N)
Photos CHAR(1) (Photos Y/N)

ShootPerformers Table
shootid INTEGER
puid INTEGER shootid + puid = Primary Key

Website Table
Wid INTEGER Primary Key
WebsiteName VARCHAR (Site Name)
shootid INTEGER
url VARCHAR (URL of the photos or video in this shoot)
startfilename VARCHAR (the filename of the first file in the sequence of files that make up this shoot)
endfilename VARCHAR (The filename of the last file in the sequence)

Movies Table
Mid INTEGER Primary Key
Mname VARCHAR (movie name)
Shootid INTEGER
SceneNo SMALLINT (Scene number that this shoot appears in)

Notes: where attribute names are the same that attribute is a foreign key ie puid and shootid
mods can be added for canadian or overseas addresses if need be
shootdate can be checked against performerDOB to validate >= 18

Ok what am I missing and who will code the front end (PHP preffered)

FightThisPatent · 06-02-2005, 05:22 PM

[QUOTE=mikesouth])

Aliases Table
puid INTEGER
alias VARCHAR puid+alias = Primary Key

------------------------------------------------------
this kind of table doesn't need a combination primary key. you can just use an auto-increment field called like AID (integer), with an index on puid and an index on alias.
----------------------------------------------------

Shoots Table
Shootid INTEGER Primary Key
ShootDate DATE (Date of Shoot)
Location VARCHAR (Shoot Location)
Video CHAR(1) (Video Y/N)
Photos CHAR(1) (Photos Y/N)

-----------------------------------------------------
probably should also include a production/shoot title, ie. the movie name, the setting of the shoot (ie. barbie on the beach with red bikini) since a shoot could be a movie or a photo spread. The other reason for having a title, is if you file for copyright, so that the work (shoot) is grouped
--------------------------------------------------------

ShootPerformers Table
shootid INTEGER
puid INTEGER shootid + puid = Primary Key

--------------------------------------
primary key should just be an auto-incremented field like name SPID (integer)

with index on shootid and index on puid
----------------------------------------

Website Table
Wid INTEGER Primary Key
WebsiteName VARCHAR (Site Name)
shootid INTEGER
url VARCHAR (URL of the photos or video in this shoot)
startfilename VARCHAR (the filename of the first file in the sequence of files that make up this shoot)
endfilename VARCHAR (The filename of the last file in the sequence)

----------------------------
since there can be multiple URL to a website table entry, would need a new table that is like WebsiteURL where you can list the multiple URLs
---------------------------

Movies Table
Mid INTEGER Primary Key
Mname VARCHAR (movie name)
Shootid INTEGER
SceneNo SMALLINT (Scene number that this shoot appears in)

i think you have it.. i would have to draw the schema on the whiteboard to make sure all the keys lined up for doing queries, but it looks good with the above comments.

i built a 2257 database a year ago,polled some content producers, they didn't have any interest in paying for a database system to organize their records. wasn't worth it, so the project got shelved.

using MySQL, FileMaker, Access, even Excel, one can organize their records to be searchable by the required fields (the 2257 regs are pretty specific on the specs for this).

There are other things to do to comply with 2257 beyond the indexing piece that Mike has presented... but its a great start to atleast doing good recordkeeping.. which is what 2257 is about (and about shutting down adult sites).

Fight the 1st Normal Form!

FightThisPatent · 06-02-2005, 05:27 PM

Quote:

Originally Posted by mikesouth

Aliases Table
puid INTEGER
alias VARCHAR puid+alias = Primary Key

The part of the regs about indexing by stage name, alias, etc is humorous because "trixie" could be a "stage name" or an "alias", etc.

By having it as you have it, the DOJ inquiry could be "show me all the records of a model that goes by the stage name of "trixie". Any alias match on "trixie" would pull up the PUID, which then could pull up the individual models info.

2257 Purists would probably argue that you have to have those exact fields defined, for which if you wanted to, you can include an extra column in the table that is TypeOfAlias varchar(20) where you could be specific.

Fight the Inquiries!

mikesouth · 06-02-2005, 05:28 PM

good points except

-----------------------------------------------------
probably should also include a production/shoot title, ie. the movie name, the setting of the shoot (ie. barbie on the beach with red bikini) since a shoot could be a movie or a photo spread. The other reason for having a title, is if you file for copyright, so that the work (shoot) is grouped
--------------------------------------------------------

that violates normal form and is there anyway in terms of movie title or website name

mikesouth · 06-02-2005, 05:30 PM

I tried to think it out logically...all in all I have maybe 2 hrs in it...Im fine with yall refining it...lets just put it in service ;)

Peaches · 06-02-2005, 05:40 PM

I get so turned on when the good looking geeks start up

Love you both, Mike and Brandon!

mikesouth · 06-02-2005, 06:46 PM

apparently I aint gonna get any takers

damn 2 hrs of my life down da drain

latinasojourn · 06-02-2005, 06:49 PM

looks good.

if a program could also pull the exif data off the camera file (and the shooter remembered to have the time set correctly in the camera) this could automatically fill in date of content creation fields for each image.

and it could also pull camera tech data at same time (useful for batch processing, color correction, under/over exposure diagnosis, focal length, etc)

mikesouth · 06-02-2005, 06:52 PM

thats a damn good idea

FightThisPatent · 06-02-2005, 07:08 PM

Quote:

Originally Posted by Peaches

I get so turned on when the good looking geeks start up

"The Dirty Database Schema"

In the course of database design, the developer could look at some sexy table, and tell it to spread its columns out, sometimes even needing to denormalize the table in order to handle loads of data input, and the epic climax of mapping the foreign keys and primary keys leads to a well formed, well curved database schema.

short story dedicated to Peaches.

Fight the moonlighting writer!

Peaches · 06-02-2005, 07:10 PM

Quote:

Originally Posted by FightThisPatent

"The Dirty Database Schema"

In the course of database design, the developer could look at some sexy table, and tell it to spread its columns out, sometimes even needing to denormalize the table in order to handle loads of data input, and the epic climax of mapping the foreign keys and primary keys leads to a well formed, well curved database schema.

short story dedicated to Peaches.

Fight the moonlighting writer!

Xenophage · 06-02-2005, 08:46 PM

Quote:

Originally Posted by Peaches

Peaches Rocks

Interesting thread thanks

Brujah · 06-03-2005, 12:11 AM

mikesouth, you should setup a Sourceforge account and project and get this started there, with a page for hosting information and files so schema's can be download. Contributors can easily login and contribute code which can be committed then. It would be a lot better than relying on a GFY thread that gets buried so easily.

datatank · 06-03-2005, 12:14 AM

Quote:

Originally Posted by Brujah

mikesouth, you should setup a Sourceforge account and project and get this started there, with a page for hosting information and files so schema's can be download. Contributors can easily login and contribute code which can be committed then. It would be a lot better than relying on a GFY thread that gets buried so easily.

You are right on the money. .Great idea.
This can really help alot of people GREAT idea

06-02-2005, 05:05 PM	#1
mikesouth Confirmed User Industry Role: Join Date: Jun 2003 Location: My High Horse Posts: 6,334	DBSCHEMA for 2257 Database I think its pretty complete...questions/suggestions are welcome Performer Table puid INTEGER Primary Key Performer_Id BLOB (id type jgp png tif gif etc) Performer_Fname VARCHAR (First Name) Performer_Lname VARCHAR (Last Name) Performer_Mname VARCHAR (Middle Name) Peformer_DOB DATE (Birth date) Performer_addy1 VARCHAR (address line 1) Performer_addy2 VARCHAR (address line 2) Performer_City VARCHAR (city) Performer_State CHAR(2) (state) Performer_zip VARCHAR (Zip code) Performer_email VARCHAR (email addy) Performer_phone VARCHAR (phone number) Aliases Table puid INTEGER alias VARCHAR puid+alias = Primary Key Shoots Table Shootid INTEGER Primary Key ShootDate DATE (Date of Shoot) Location VARCHAR (Shoot Location) Video CHAR(1) (Video Y/N) Photos CHAR(1) (Photos Y/N) ShootPerformers Table shootid INTEGER puid INTEGER shootid + puid = Primary Key Website Table Wid INTEGER Primary Key WebsiteName VARCHAR (Site Name) shootid INTEGER url VARCHAR (URL of the photos or video in this shoot) startfilename VARCHAR (the filename of the first file in the sequence of files that make up this shoot) endfilename VARCHAR (The filename of the last file in the sequence) Movies Table Mid INTEGER Primary Key Mname VARCHAR (movie name) Shootid INTEGER SceneNo SMALLINT (Scene number that this shoot appears in) Notes: where attribute names are the same that attribute is a foreign key ie puid and shootid mods can be added for canadian or overseas addresses if need be shootdate can be checked against performerDOB to validate >= 18 Ok what am I missing and who will code the front end (PHP preffered) __________________ Mike South It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz. Last edited by mikesouth; 06-02-2005 at 05:07 PM..

06-02-2005, 05:22 PM	#2
FightThisPatent Confirmed User Join Date: Aug 2003 Location: Austin, TX Posts: 4,090	[QUOTE=mikesouth]) Aliases Table puid INTEGER alias VARCHAR puid+alias = Primary Key ------------------------------------------------------ this kind of table doesn't need a combination primary key. you can just use an auto-increment field called like AID (integer), with an index on puid and an index on alias. ---------------------------------------------------- Shoots Table Shootid INTEGER Primary Key ShootDate DATE (Date of Shoot) Location VARCHAR (Shoot Location) Video CHAR(1) (Video Y/N) Photos CHAR(1) (Photos Y/N) ----------------------------------------------------- probably should also include a production/shoot title, ie. the movie name, the setting of the shoot (ie. barbie on the beach with red bikini) since a shoot could be a movie or a photo spread. The other reason for having a title, is if you file for copyright, so that the work (shoot) is grouped -------------------------------------------------------- ShootPerformers Table shootid INTEGER puid INTEGER shootid + puid = Primary Key -------------------------------------- primary key should just be an auto-incremented field like name SPID (integer) with index on shootid and index on puid ---------------------------------------- Website Table Wid INTEGER Primary Key WebsiteName VARCHAR (Site Name) shootid INTEGER url VARCHAR (URL of the photos or video in this shoot) startfilename VARCHAR (the filename of the first file in the sequence of files that make up this shoot) endfilename VARCHAR (The filename of the last file in the sequence) ---------------------------- since there can be multiple URL to a website table entry, would need a new table that is like WebsiteURL where you can list the multiple URLs --------------------------- Movies Table Mid INTEGER Primary Key Mname VARCHAR (movie name) Shootid INTEGER SceneNo SMALLINT (Scene number that this shoot appears in) i think you have it.. i would have to draw the schema on the whiteboard to make sure all the keys lined up for doing queries, but it looks good with the above comments. i built a 2257 database a year ago,polled some content producers, they didn't have any interest in paying for a database system to organize their records. wasn't worth it, so the project got shelved. using MySQL, FileMaker, Access, even Excel, one can organize their records to be searchable by the required fields (the 2257 regs are pretty specific on the specs for this). There are other things to do to comply with 2257 beyond the indexing piece that Mike has presented... but its a great start to atleast doing good recordkeeping.. which is what 2257 is about (and about shutting down adult sites). Fight the 1st Normal Form! __________________ http://www.t3report.com (where's the traffic?) v5.0 is out! \| http://www.FightThePatent.com \| ICQ 52741957

06-02-2005, 05:28 PM	#4
mikesouth Confirmed User Industry Role: Join Date: Jun 2003 Location: My High Horse Posts: 6,334	good points except ----------------------------------------------------- probably should also include a production/shoot title, ie. the movie name, the setting of the shoot (ie. barbie on the beach with red bikini) since a shoot could be a movie or a photo spread. The other reason for having a title, is if you file for copyright, so that the work (shoot) is grouped -------------------------------------------------------- that violates normal form and is there anyway in terms of movie title or website name __________________ Mike South It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.

06-02-2005, 05:30 PM	#5
mikesouth Confirmed User Industry Role: Join Date: Jun 2003 Location: My High Horse Posts: 6,334	I tried to think it out logically...all in all I have maybe 2 hrs in it...Im fine with yall refining it...lets just put it in service ;) __________________ Mike South It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.

06-02-2005, 06:46 PM	#7
mikesouth Confirmed User Industry Role: Join Date: Jun 2003 Location: My High Horse Posts: 6,334	apparently I aint gonna get any takers damn 2 hrs of my life down da drain __________________ Mike South It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.

06-02-2005, 05:40 PM	#6
Peaches Old broad Join Date: Oct 2002 Location: Away Posts: 13,933	I get so turned on when the good looking geeks start up Love you both, Mike and Brandon!

06-02-2005, 06:49 PM	#8
latinasojourn Confirmed User Join Date: Oct 2003 Posts: 3,191	looks good. if a program could also pull the exif data off the camera file (and the shooter remembered to have the time set correctly in the camera) this could automatically fill in date of content creation fields for each image. and it could also pull camera tech data at same time (useful for batch processing, color correction, under/over exposure diagnosis, focal length, etc)

06-02-2005, 06:52 PM	#9
mikesouth Confirmed User Industry Role: Join Date: Jun 2003 Location: My High Horse Posts: 6,334	thats a damn good idea __________________ Mike South It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.

06-03-2005, 12:11 AM	#13
Brujah Beer Money Baron Industry Role: Join Date: Jan 2001 Location: brujah / gmail Posts: 22,157	mikesouth, you should setup a Sourceforge account and project and get this started there, with a page for hosting information and files so schema's can be download. Contributors can easily login and contribute code which can be committed then. It would be a lot better than relying on a GFY thread that gets buried so easily. __________________ Free Premium Domain Lists and Tools at Clickmojo.com For Sale: Obscenity.com