Database Structure

[Josh Davis]

i had just a quick question about database structure. In order to maintain
referential integrity in a database how would i set up a database in
Microsoft Access 2007 for a DVD Movie Store. All the while, allowing tables
to be concise enough to allow for tables to be filled in by Genre (Comedy,
Action, Suspense, & etc). Also to allow for more movies to be added in
alphabetical order without messing up the ascending ID numbers assuming ID
numbers would be necessary. Maybe you could send me a typed up diagram of how
it should be set up to be accurate to Database Management Standards. I would
really appreciate any light you can shed on this if you would? I would
appreciate it if anyone would create the structure in Access and then email
it to me at (e-mail address removed). Thank you in advance.

 

[TonyT]

Hi Josh,

You said you have a quick question, but I don't see a question at all, just
a request for someone to do your (home)work for you.

Perhaps if you ask a question you may get a helpful response.

TonyT

 

[Josh Davis]

Allow me to rephrase: I am creating a database for a SecondLife Company. They
are primarily a DVD company in SecondLife. I just want to make sure I have
everything right structure wise before I continue. Yes I do have a question.
No i am not asking you to do my "(home) work". My question is this when you
setup a database and want to maintain referential integrity, in the tables
how do you create them as far as your ID or unique identifier for every
record goes? Do you create each table for say "Movies", "Section",
"Category", & "Location then assign them all a numeric ID for each: Title,
Section, Category, & Location in each table respectively, then add those
reference numbers to each table as needed? Or do you use those numbers in
separate tables then in a query the reference IDs will come into play there?
Thank you. Hope that clears things up!

 

[Michael Gramelspacher]

Allow me to rephrase: I am creating a database for a SecondLife Company. They
are primarily a DVD company in SecondLife. I just want to make sure I have
everything right structure wise before I continue. Yes I do have a question.
No i am not asking you to do my "(home) work". My question is this when you
setup a database and want to maintain referential integrity, in the tables
how do you create them as far as your ID or unique identifier for every
record goes? Do you create each table for say "Movies", "Section",
"Category", & "Location then assign them all a numeric ID for each: Title,
Section, Category, & Location in each table respectively, then add those
reference numbers to each table as needed? Or do you use those numbers in
separate tables then in a query the reference IDs will come into play there?
Thank you. Hope that clears things up!

Just to note that Microsoft has a DVD template database:
http://office.microsoft.com/en-us/templates/TC010186441033.aspx?pid=CT102144001033

 

[Armen Stein]

Allow me to rephrase: I am creating a database for a SecondLife Company. They
are primarily a DVD company in SecondLife. I just want to make sure I have
everything right structure wise before I continue. Yes I do have a question.
No i am not asking you to do my "(home) work". My question is this when you
setup a database and want to maintain referential integrity, in the tables
how do you create them as far as your ID or unique identifier for every
record goes? Do you create each table for say "Movies", "Section",
"Category", & "Location then assign them all a numeric ID for each: Title,
Section, Category, & Location in each table respectively, then add those
reference numbers to each table as needed? Or do you use those numbers in
separate tables then in a query the reference IDs will come into play there?
Thank you. Hope that clears things up!

Hi Josh,

You got the push-back because DVD rental is a classic school homework
scenario. Looks like you actually have a real-world (well, SL real)
example.

To quickly answer your question, though whole books are written about
database design (see Database Design for Mere Mortals for a very
accessible example):

Each table has a primary key - that's the unique identifier.

Any table that refers to it has a foreign key - that's the "link" to
the the primary key in the other table. Join queries bring them all
together.

So:

tblMovie
MovieID (primary key)
MovieTitle
CategoryID (foreign key)
etc....

tblCategory
CategoryID (primary key)
CategoryName
etc...

HOWEVER: You've got to work through the complexity of the database
design before you go typing all this into Access. The best way is
paper & pencil or whiteboard. Lay out all your tables as circles and
connect them with one-to-many lines. When it all makes sense, then
you can build the database.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com