Hello everyone, I want to create a web application where an artist can register with us and create their own profile which others can view. The site should also enable members to view the names of all the albums produced by said artists and confirm if the serial number for the CDs they purchased is valid. So my question is what would be the best suitable table structure for storing all the albums an artist has produced and and ther valid serial numbers for each album. Please please assist I will greatly appreciate your input. Thanx in advance.
mmnewbee 0 Newbie Poster
BitBlt 452 Practically a Master Poster Featured Poster
Simplest solution would be an Artist table with ArtistID as primary key, then whatever you want to show about the artist him/herself...then an Album table with AlbumId as the primary key, ArtistId as a foreign key, then whatever you want to save about the Album itself. For instance, a serial number (probably a string datatype to hold combinations of numbers/letters/dashes/slashes or whatever).
Keep in mind this is a VERY simplistic structure. It does not allow for multiple artists on one album, for example. But it should get you started.
Hope this helps! Good luck!
mmnewbee 0 Newbie Poster
Thanks alot for your contribution BitBit. What I want to know is what is the most suitable table structure to store multiple serial numbers for the same album name.
BitBlt 452 Practically a Master Poster Featured Poster
If you can guarantee that you'll never have more than, say, 5 serial numbers for any given album, you could use a de-normalized table by just having multiple nullable columns on your Album table called "Serial1", "Serial2", etc. You automatically constrain your database, though...and the first time someone has ONE MORE Serial number than you can accommodate, they're out of luck.
If you want a normalized table structure, you can create a child table off of the Album table. Call it "AlbumSerialNumbers". I would give it a unique id, then a foreign key to Album. Include SerialNumber as a non-key column. If the SerialNumber is unique across all possible albums (like ISDN is for a published book), put a "Unique" constraint on that column to prevent having the same serial number refer to different albums.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.