Best possible layout for new db?

[Edm]

Hi,

Im putting together a database to track inventory and
sales of some records. Im trying to group the records by
artist, and then individual record within the artist group.

The end result im shooting for is to be able to display,
on the web, each groups respective work on a single page,
individually from all other groups.

Any tips on how to start this one off on the right foot?

-Edm

 

[Pavel Romashkin]

My opinion is that Access per se is not designed to deliver information
over the Web. You will be better off using another database, such as
MySQL, along with a scripting language such as PHP.
Access is a great RAD tool, and if you are intimately familiar with it,
you will greatly benefit from designing the DB in Access to upload data
to a web database. But don't expect that an Access database will be easy
to integrate with a web based environment.

Pavel

 

[Jay Vinton]

The end result im shooting for is to be able to display,

on the web, each groups respective work on a single page,
individually from all other groups.

Hi Edm,

Each group can have more than 1 atiist and each artist can belong to more than 1 group. Each record can have more than 1 group and each group can perform on more than 1 record. Etcetera.

These many-many relations must be resolved with intermediary tables with compound PKs. Try something like this. Your base tables are Artists, Groups and Records.

Artists
-------
ArtistID (Long PK)

Groups
--------
GroupID (Long PK)

Records
---------
RecordID (Long PK)

ArtistsGroups
---------------
ArtistID (Long PK) (FK to Artists)
GroupID (Long PK) (FK to Groups)

RecordsGroups
-----------------
RecordID (Long PK) (FK to Records)
GroupID (Long PK) (FK to Groups)

A solo artist would belong to a group with the same name.

You may also want to add another intermediary table to account for a solo artist playing on a few tracks of a record made by a group that the artist doesn't belong to.

RecordsArtists
-----------------
RecordID (Long PK) (FK to Records)
ArtistID (Long PK) (FK to Artists)

If you want to resolve down to the track level, you'll have to extend this model with the same principles.

This is an example of a query that would return records made by a group, or group that performs on a record. Add your own SELECT list and WHERE clause to limit the results.

SELECT .... FROM
Records INNER JOIN (Groups INNER JOIN RecordsGroups ON Groups.GroupID = RecordsGroups.GroupID) ON Records.RecordID = RecordsGroups.RecordID
WHERE ...;

Hope that helps.

Jay

 

[Jay Vinton]

The end result im shooting for is to be able to display,

on the web, each groups respective work on a single page,
individually from all other groups.

Hi Edm,

Each group can have more than 1 atiist and each artist can belong to more than 1 group. Each record can have more than 1 group and each group can perform on more than 1 record. Etcetera.

These many-many relations must be resolved with intermediary tables with compound PKs. Try something like this. Your base tables are Artists, Groups and Records.

Artists
-------
ArtistID (Long PK)

Groups
--------
GroupID (Long PK)

Records
---------
RecordID (Long PK)

ArtistsGroups
---------------
ArtistID (Long PK) (FK to Artists)
GroupID (Long PK) (FK to Groups)

RecordsGroups
-----------------
RecordID (Long PK) (FK to Records)
GroupID (Long PK) (FK to Groups)

A solo artist would belong to a group with the same name.

You may also want to add another intermediary table to account for a solo artist playing on a few tracks of a record made by a group that the artist doesn't belong to.

RecordsArtists
-----------------
RecordID (Long PK) (FK to Records)
ArtistID (Long PK) (FK to Artists)

If you want to resolve down to the track level, you'll have to extend this model with the same principles.

This is an example of a query that would return records made by a group, or group that performs on a record. Add your own SELECT list and WHERE clause to limit the results.

SELECT .... FROM
Records INNER JOIN (Groups INNER JOIN RecordsGroups ON Groups.GroupID = RecordsGroups.GroupID) ON Records.RecordID = RecordsGroups.RecordID
WHERE ...;

Hope that helps.

Jay