A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Database Structure



 
 
Thread Tools Display Modes
  #1  
Old September 19th, 2009, 08:29 AM posted to microsoft.public.access.tablesdbdesign
Josh Davis
external usenet poster
 
Posts: 15
Default Database Structure

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 . Thank you in advance.
  #2  
Old September 19th, 2009, 08:46 PM posted to microsoft.public.access.tablesdbdesign
TonyT
external usenet poster
 
Posts: 154
Default Database Structure

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" wrote:

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 . Thank you in advance.

  #3  
Old September 19th, 2009, 09:58 PM posted to microsoft.public.access.tablesdbdesign
Josh Davis
external usenet poster
 
Posts: 15
Default Database Structure

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!
"TonyT" wrote:

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" wrote:

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 . Thank you in advance.

  #4  
Old September 19th, 2009, 11:00 PM posted to microsoft.public.access.tablesdbdesign
Michael Gramelspacher
external usenet poster
 
Posts: 482
Default Database Structure

On Sat, 19 Sep 2009 13:58:02 -0700, Josh Davis wrote:

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!
"TonyT" wrote:

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" wrote:

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 . Thank you in advance.


Just to note that Microsoft has a DVD template database:
http://office.microsoft.com/en-us/te...CT102144001033
  #5  
Old September 19th, 2009, 11:01 PM posted to microsoft.public.access.tablesdbdesign
Armen Stein
external usenet poster
 
Posts: 507
Default Database Structure

On Sat, 19 Sep 2009 13:58:02 -0700, Josh Davis
wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:27 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.