Access database/table design question

[Fullmetal Chocobo]

I'm trying to figure out the best way to work this out. I'm creating a database for maintaining hardware information about my systems. I would like the flow to go
machine > volume > disk > equipment. However, the disk could consist of single disk or RAID array configurations. I can't validate relationships if I have the disk going to two tables as far as I know either.

I'm new at this, and am trying to figure things out for myself, but I'm a bit stuck on this one. Here is my relationship map so far.

EDIT:
Apparently I'm not getting the concept of relational tables as they are meant to be, as the person I'm working with is saying that I'm designing my tables all wrong. I put phone numbers in one table, name information in another, etc., and he said that they should all be in one table.

Is there a book or resource recommended for database / table design? I'm going to be doing all of this in Access 2007 until I get the hang of it, and then I'll migrate to MySQL.

 

[EagleKeeper]

Merge the RAID and Single into just one table with some unused fields depending on which it is.

 

[Fullmetal Chocobo]

Okay. I've been trying to figure out how to optional fields. I didn't know if designing it with some fields empty would be bad design. Thanks.

 

[Crusty]

Just set the default value to NULL for the optional fields, and only populate them if you need to. It's quick and easy to check for NULL in queries 🙂

 

[EagleKeeper]

Originally posted by: Fullmetal Chocobo
Okay. I've been trying to figure out how to optional fields. I didn't know if designing it with some fields empty would be bad design. Thanks.

No design ever survives a brush with reality.

 

[JACKDRUID]

Originally posted by: Fullmetal Chocobo
I didn't know if designing it with some fields empty would be bad design. Thanks.

its not... as long as it defaults to something that is consistant.

I would put all fields in disk_raid and disk_single into a new table disk_spec, add a [type] field to disks table to specify whether its a raid or single.

 

[Fullmetal Chocobo]

As per the edit, is there a source that ya'll would recommend for learning how to do this stuff correctly? Either a book about Access 2007 or purely about database design. Or maybe a website? I haven't been able to find anything thus far. If not, I'll pick up a few books on Access 2007 and start there.

 

[KLin]

I think you need to do some reading on the fundamentals of a RDBMS(Relational Database Management System).

In regards to your edit, the person is correct. If you are creating a reference table of contact information, you would want to store phone numbers in the same table, unless a contact could have multiple phone numbers(work phone, cell phone, fax, etc.). In that case the main contact table would contain all the information for the contact, and you would want a child table that can store multiple phone numbers for a single contact.

See example below:

1. tblContact would be the main table for contact information containing fields like RecordID(autogenerating number), ContactName, Address, City, State, etc. etc.
2. tblContactPhoneNumber would have RecordID, ContactID(a foreign key constraint to RecordID in tblContact), PhoneNumberDesc(work, cell, fax), PhoneNumber, etc etc

In Microsoft Access, you have the ability to link tables together in a form to manage child data. You would create a form with the data source as tblContact, then create a subform with the datasource of tblContactPhoneNumber, add it to the main form, and link the subform to the main form using the ContactID to tblContact.RecordID as your link

You may want to draw up the layout on a piece of paper just so you can visualize how it needs to be setup before actually setting up the table structure.

 

[Fullmetal Chocobo]

Originally posted by: KLin
I think you need to do some reading on the fundamentals of a RDBMS(Relational Database Management System).

🙂 I definitely want to. I'm literally just starting out with this stuff, so I'm fusterclucking my way through it right now. Is there any books you would recommend? I'll start searching for books on RDBMS specifically and see what I can find.

 

[KLin]

http://www.amazon.com/s/ref=nb...onal+databases&x=0&y=0

I can't recommend any specific books as most of my knowledge has been hands on(and searching groups.google.com extensively 😉). I would check out any of the highly rated books in the link above.