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)

[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!

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!

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

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 ;)

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

Love you both, Mike and Brandon!

apparently I aint gonna get any takers


damn 2 hrs of my life down da drain

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)

thats a damn good idea

"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 Rocks :)

Interesting thread thanks

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