Can this small table be broken dow into smaller tables?

[djclinton15]

Hi. Once again, I’m trying to learn (or rather teach myself how to build a relational database using Primary & Foreign Keys. Yes, I have watched countless videos on building a relational database as well as gone through a few free online courses. Now, I’m not ashamed to say that my next statement will make me look really stupid, or at least that’s how I feel. Multiple times I have unsuccessfully tried to create an Access database/building tables based upon using Primary & Foreign keys along with other struggles. For instance, the few databases I have built, which I assure you are far from Normalization, but I managed to join tables by a 1-common text field amongst other tables. I believe I have somewhat of an understanding of Normalization, though probably not as clearly as I ought to have. I know one of the normalization rules (if not the first rule) is not to have duplicate fields/attributes in more than one table & each table should only represent items only related to that table.



So, I started creating my first table that represents all of the information I collected from each musician artist which I frequently listen to & I’m talking about over 260 artists (or records). I’ll list each of my fields within my table, provide you with the Data Type, & a brief description. I’m looking to see if this table can be broken down into smaller tables & how to relate the tables to one another. The name of this table is called, ‘tblArtist’. Here are the following fields within this table:

Field Nam	Data Type	Description
ArtistID	Auto Number	Primary Key
ArtistAbev	Short Test	An abbreviation of the artist’s full name
ArtistName	Short Text	The artist’s full name
Type	Short Text	If the artist is a ‘Group’ or a ‘Person’ etc1
Gender	Short Text	Female or Male (of course
Born	Date/Time	If Type is a Person, then input artist’s DOB
Area	Short Text	The area where the artist is from2
Genre	Short Text	Genre type3
Website	Hyperlink	The website where I obtained the artist’s information

¹At first I made this field as a was a Lookup Field from another table, tblType
²At first I made this field as a was a Lookup Field from another table, tblGerne
³At first I made this field as a was a Lookup Field from another table, tblArea

On second thought, I didn’t think making the Type, Genre, & Area LookUp fields was a smart idea for several reasons. 1) I was having duplicate fields in more than one table. 2) I create a datasheet form (from the tblArtist, which is my central table/form) & I already had combo fields for ‘Type’, ‘Genre’, & ‘Area’ (this is before I changed those fields from LookUp fields to text field & what was most discouraging as well as frustrating was while adding data to the frmArtist, when I needed to add a new data to Type, Genre, or Area fields, I first needed to put in the new data into the corresponding table. Let me try explaining myself better. If I had a new area to add to one of my Records in the frmArtist, if that particular area isn’t already listed in the tblArea, I couldn’t add that new area in the frmArtist until I added the new area into the tblArea. Then I would need to refresh the frmArtist in order to see & choose the new area in the Area Combo Box. I would need repeat the same process if I needed to add a new Genre &/or type. I remember working with Forms & Combo boxes before where I could directly add new content within the Combo Box, but I don’t recall how it was set up. One last comment, I thought to make the tblArea into a query & then try to create the area Combo Box… Since the area query was non-editable I knew that the Area Combo Box would be noneditable too.
If by chance anybody could kindly take a look at my one table & tell me if it can be broken down into smaller tables? I can’t see how because it’s a pretty small tables for starters.

Any advice/suggestions would be greatly appreciated! Thanks in advance!

[Micron]

I don't see much opportunity except to say that if it were my db I would not bother with area or gender as they are of no importance for this, but that's just me. It would eliminate some of the issue you describe. The rest could be handled by using combobox not in list event. You do need to realize that using combos in data forms runs the risk of inadvertently changing values when you play with them. If you're going to be the sole user and you know this, then it shouldn't be a problem.

Have you not seen the likes of this?
http://access.mvps.org/access/lookupfields.htm

EDIT - re area: what would you put if the artist is a band & one comes from Germany, another from UK, another from France, etc. ? That's one reason I think it's not important, but it is your db after all.

[Edgar]

You don't always need a technical response. Different database models have their own fans. Even with years of experience, it's normal to question which model is best for your storage needs and others may see things you don't, but vice versa also applies. Now, can my response get any more generic? you bet: database modeling depends on your requirements. And often times, you should consider what another developer would think while trying to work on your database. Now, you have written your requirements in that table with your fields, but...
ArtistID: OK
ArtistAbev: is this YOUR abbreviation, an official one, a fans one or a short name used only within the database? Name it accordingly.
ArtistName: OK
Type: type of what? as per your description, it could be renamed to just "group_size", "members_count" or "artist_count" and have the frontend show the user whether it is solo if 1, a duo if 2, a group if > 2
Gender: if one of your Artists is "Garbage", what gender is this? the singer is a female, but the rest of the group is 3 dudes. Also, this is the 21st century, so you should consider at least another type of gender, and you definitely need a Genders table.
Born: again, you have a problem here when it's a group, so there's a strong indication that you need a separate table for the people who belong to the artist.
Area: Area of what? the area the band covers? the geographical area where they play? where they initiated? is it another type of Area? Oh, it's the "Origin". Who's origin though? Again, a separate table for the people who belong to the artist. And you need at least a Countries table here. Or is it local music? then States table? Cities table?
Genre: What do you do when the artist wrote a Funk music in the 90s, and they're currently making Reggaeton music? There's a strong indication that this should belong to a Songs table. And you need a Genres table here.
Website: Do you mean the Official website? what about their social network presence? There's a strong indication that it needs a table for that as well.

Let me know what you think

[djclinton15]

Hi Micron. I just wanted to say thanks for responding to my post. I probably didn’t give you much to work with & what I did give to you probably didn’t make any sense to you. Yes, would be the sole user for this db. All I want to tell you is, however, I respond to Edger I truly me no negative reflection on you. I’m not here to any waves/trouble with anybody/ that’s just not my style. It’s unlikely for you to remember me, but over the years you have helped me out of some scrapes & I appreciated your help in the past. BTW, I just noticed that I misspelled, “down” in my post title. I must look like an idiot right from the start. Take care.

[djclinton15]

Hi Edger, thanks for replying to my post. I wanted you to know that I'm in the middle of addressing all of your questions, but it may take a day or two to get back to you. Hope that will be fine.

[Edgar]

Hi dj, do tell me how it's going when you can

[djclinton15]

Hi Edger. I appreciated your reply to my post as well as you’re seeming to be so open-minded to my objection here. Sorry, it took me a while in getting back to you; some personal issues came up. I’ll get right into addressing some of your questions & will do my best in explaining my reasons. I’ll just start you out with my first table (or what I think of as my main table called, ‘tblArtist’. This table will contain various information per artist. Within this table, I will include a hyperlink field of the website where I obtained the artist’s information. I know that data ought not to be entered directly through Tables, but rather via. Forms. You may find a lot of my methods confusing & even senseless, but it is accustomed to how I work with Access. For instance, most of the Forms I create & regularly use are Datasheet Forms & I know that almost everyone would ask me why I use Datasheet Forms when I can enter my data straight into the queries. I have my reasons why I use Datasheet Forms & which I will personally share with you as we go along, I just prefer using Datasheet Forms. In addition, I like using Combo Boxes in my Datasheet Forms solely to reduce keystrokes. Again, I’ll try to explain more along the way.

1. My first field in this tblArtist is, ‘ArtistD’ & the Data Type in this field is, AutoNumber/Long Integer. I suppose that since I’m trying to rebuild a similar database (related material), I’m hoping to build this database as more of a Normalized Relational Database. Therefore, I supposed that I ought to make this ‘ArtistID’as the Primary Key. I only question myself & hesitate because, in the other similar database, I always had more success assigning the next field as the Primary Key. I’ll try explaining.

2. The next field in this tblArtist is, ArtistAbrev. This is also a unique field that is just comprised of an abbreviation of the Artist’s name, which I’d created myself. Usually, my method of abbreviating the Artist’s names (right or wrong) is by leaving out all of the spaces & vowels – unless a name either begins or ends in a vowel, then I’ll include the vowel. For example, in the group “Garbage” (great band, BTW) I may abbreviate Garbage to something like, ‘Grbge’. I’ll give you a few more examples, Nine Inch Nails = ‘NneInchNls’ 3 Doors down = ‘3DrsDwn’ & Audioslave = ‘Adslve’. I know that many people would see my method of abbreviating artist names as senseless & would make 10 other different suggestions (which I might be open to) but these artist abbreviations were created by me in a denormalized related database I created over ten years ago. I still use that database, but I have learned so much over the years, & like I’ve been repeatedly saying, I want to see if I can rebuild a similar database using Primary & Foreign Keys. Ten years ago I was struggling greatly trying to create relationships between my tables with using Primary & Foreign Keys when a member on this very website/forum suggested that I ought to try to join/relate my tables by selecting (or creating) one common text field as becoming my Primary Key ID field in all of my tables. In other words, they told me to disregard Primary & Foreign Keys & to create one unique field which you can use to join other tables. So, I used the ‘ArtstAbev’ field or at the time I had just named that particular field, ‘ArtistID’ & it was a text field vs. a number field, which greatly helped me in relating my tables at the time. When I added that ‘ArtistID’ field (which remember is a text field) into my other tables, then I was able to join my tables based on the ‘ArtistID’ field. I’m not sure if you’re following what I’m saying, but I was able to build 1 or 2 other databases with different data, but based on using the same method that a member had shown me. I don’t know how common & often this method might be used in relating fields & tables.
Although this is irrelevant, I still remember who that member was who suggested this technique to me & they still come onto this site/forum, but I would never mention the member’s name. For several reasons, 1) I always sensed this member to be a female & took her as a private/disclosed person, & I always tried to respect her in that regard. 2) I think that I can say for sure how it is very unlikely this member would remember me nor remember telling me what I just describe. 3). This particular member had helped me countless times in which I learned so much from her (amongst so many other members on this site) I worked with this particular member many times over the years & I just know that they would not want to be mentioned or recognized here. This member (like so many other members here) was ALWAYS very respectful very professional, strictly business & no small talk regardless of all of the times they helped me out of jams.
Anyway, moving on, some people might ask me why aren’t you implementing that same method again since I had so much success with it in the past. And my answer would be, that I want to learn & try to rebuild the database from scratch using Primary & Foreign Keys.

3. The third field for this tblArtist is simply the artist’s name fully spelled out. Eg. ‘LdZppln’ = Led Zepplin, or ‘Qn’ = Queen, or ‘Hrt’ = Heart or ‘TheRllngStns’ = The Rollng Stones. Not to keep on repeating myself, but these abbreviated artist names go back over ten years ago when I think come to think of it.

4. My next field in this tblArtist is, Type, I only have come across two types while I’m inputting the data. Right now, all I have is the ‘ArtistAbrev’ & ‘ArtistName’ fields completely filled in from another database. The rest of the fields I’m filling in as I go along. Except, I’ve gotten myself sidetracked with trying to correctly build & join tables. Thus far, the 2-types I have is, ‘group’ & ‘person’. Let me stop here for a minute & inform you where I’m getting my information (data) from. As I’m sure how aware you are of the thousands of websites out there that have any information on any particular artists. Be mindful, the website I use is, MusicBrainz - The Open Music Encyclopedia. Note, in my datasheet form I created a value list Combo Box for the Type field since I only have two items in the Type field. I’ll explain to you in just a few minutes why I’m so big on having Combo Boxes in my datasheet forms.

5. Moving on to the Gender field & the Born field; both of these fields are dependent upon what is selected in the ‘Type’ field. Note, thus far the ‘ArtistAbrev’ & ‘ArtistName’ fields are required fields. Whereas, the ‘Gender’ & the ‘Born’ fields are purposely NOT set as required fields. The reason being is, if the ‘Type’ field is a Person then I’ll put in that person’s gender & when they were born (DOB). Otherwise, if the ‘Type’ is a Group (or a Band) then I’ll leave the ‘Gender’ & the ‘Born’ fields blank/empty. If this can create an issue with the database let me know, but that is just how I would like my table to be. And I want to try to keep this database as simple as possible. I’m referring to the gender field which I just want to leave as, “Female” or Male.” I believe in your post you said that I ought to have a gender table, which I can’t see why? If I wanted to create a Combo Box on ‘Gender’ in a form I would just use a value list since there are only 2-values in the gender Combo Box. I would have a gender Combo Box in my form. You might ask me why, if the gender field wasn’t a cbx, then I would need to fully type in, “Female” or “male.” If the ‘gender’ cbx already has the values, “Female” or “Male.” Now when I need to input either a female or male, I’ll just will either hit the letter “F” & female will fill in. Likewise with males if I just hit, I hit the letter, “M.” And, if we can revert back to the ‘Type’ field, if I’m in this field & just type the letter “G” then the word, “Group” will fill in. within the same field, if I type the letter “P” then the word, “Person” will fill in that field. Make sense?
If I can take a minute out to share something personal about myself that I think relates to my last sentence. I'm physically disabled/quadriplegic, I type with a head stick/pointer, & am not a fast typist. In addition, I cannot use a mouse & I need to use the numeric keypad, “mouse keys” to maneuver the cursor around the screen. So, maybe now you may have a clearer understanding regarding the reason why I like using Combo Boxes in any type of Forms. I hope that you can see how any little reduction (or shortcut) in typing, etc. is a tremendous advantage for me.

6. Next on the Artist table field list is Genre. Genre is a tricky field & I say this because I visited many websites pertaining to the genre & found very few artists are only assigned just one genre. The website I’m using to obtain information on artists might list a various number of different genres per artist. In my Genre field, I just want to list one genre. So, what do I do? Usually. I just pick the first or two genres in the list & move on to the next artist in the table (or form). Through my years of pretty much self-training in Access I know how Access strongly advises users not to enter or edit data within the Table itself, but to rather use Forms. I always use forms, & again I tend to mostly tend on only use Datasheet Forms. I realize & want to apologize for keep repeating certain things, but I do it because I feel like they need to be stressed upon. I know most people would tell me I was senseless, but I do have my reasons. Please don’t ask me why. Anyway, back to the genre for a minute, maybe you can come up with a better system, but like I said I only want to narrow it down to one genre per artist. Right or wrong.

7. The Area field This field is the country in which the artist began. The website I’m using also gives you the city/town & the state (or providence) where the artist began, but like I said I want to keep this simple as possible, so I’ll just stick with the countries. I would also want this field to be a Combo Box.

8. The last field in this tblArtist comes, the Website field. Yes. I already gave you the link to the website, MucisBrainz (MusicBrainz - The Open Music Encyclopedia), which again is where I’m collecting the data for my database. Except, in this field, I want to have the website link for each artist. The link I just gave you is only the first page with detailed information about the website. Like most other similar content websites, I need to click on the “Search” bar (or area) & type in the artist’s full name, “Nine Inch Nails”. A second page will appear with a list of various names/links related to the artist you entered into the search area/box on the previous page. Usually, it’s the first name/link to click on which you go onto a third page provides you with all sorts of information on the particular artists you’re searching for. Including all of the albums the artist had produced & the years, all of the members' names, etc. I want to store this website link for later use. Let me illustrate for you. On this wesite, MusicBrainz, if I want to search for the artist, Garbage I first type in "Garbage" & hit Enter. Like I said, on the second page I need to find & cliick on the closest (or the most likely) link for Garbage, & here is what the third page looks like, Garbage - MusicBrainz . Depending on how long you hang in with me along with how frustrated I get, I have lots of ideas I want to do with this database, which means I’m looking long-term project.

Edger, I know that I have thrown a ton of information that you will need to decipher, which I understand. But if I can I need to tell you a few additional things. I already create a few other small tables like an Area Table, a Genre Table, & a Type Table, but I have two problems with them: 1) I don’t know what to do with them & 2) I’m repeating field names. For instance, I already have a field name in the tblArtist, called ‘Area’ but I have that same name in the tblArea. Likewise, with the tblGenre & the tblType. All three tables just have two fields in them; an integer field & a short text field with data related to the tables. A. I don’t know which field to assign as the Primary Key. B. I have no idea how to go about creating relation amongst all of these tables.

Furthermore, for just a minute. I’m having great/lots of trouble with the Type field. First, I must tell you in the relationships between my two tables. I opened up the Relational window & chose the tblArtist & the tblType tables. In the tblType, I have just 2-fields, ‘’TypeID’, which is an integer & a ’Type’ (Group or Person). Although, I currently have the TypeID set as the Primary Key, I’m thinking that maybe I ought to change the Primary Key to the Type field instead since that is a unique field also. The relationship I created between these two tables is a one-to-many relationship – one ‘Type’ from the tblType -to- many ‘Type’ in the tblArtist. In my Datasheet Form I created a Combo Box for the ‘Type’ field from the tblType. So, in the dtasht form I’m able to select, “Group” or “Person” in the ‘Type’ field. However, when I open up the tblArtist itself, in the ‘Type’ field it doesn’t display the type (Group or Person),but instead it has he ‘TypeID’. It will either have a “1” if it’s a Group or a “2” if it’s a Person. It’s really messed up & I’m sure it has something to do with how I related the 2-tables.

Edger, I have literally dumped a ton on you & I’m not expecting to hear a response back from you for a few days. That is, if you respond back to me at all. �� Thanks for as much time as you can give me! dj

[CarlettoFed]

Maybe if you attach the file it's easier and quicker to show you how to do it.

[Edgar]

ArtistAbrev is a unique field I’d created myself by leaving out all of the spaces & vowels – unless a name either begins or ends in a vowel, then I’ll include the vowel.

That might work, but what do you do when abbreviations are repeated? What if a band name is "Lean Ground" and another "Luna Grande"? I'm making that up, but it could happen. I know, your list of artists is unlikely to have one that repeats in this field, I'm just saying that could be a problem one day. Like, imagine you make your frontend create that abbreviation, it could easily be done, just program those rules. But then you'll have to have workarounds in place for when the repetition happens, and you will have to break the rules by perhaps adding a number. Like, using my example: LnGrnd and LnGrnd1.

Ten years ago I was struggling greatly trying to create relationships between my tables with using Primary & Foreign Keys when a member on this very website/forum suggested that I ought to try to join/relate my tables by selecting (or creating) one common text field as becoming my Primary Key ID field in all of my tables. In other words, they told me to disregard Primary & Foreign Keys & to create one unique field which you can use to join other tables.

Yes that can be done if the design demands it.

I want to learn & try to rebuild the database from scratch using Primary & Foreign Keys.

That's definitely preferable.

the website I use is, MusicBrainz - The Open Music Encyclopedia.

Gender & Born fields depend on the ‘Type’ field. ‘ArtistAbrev’ & ‘ArtistName’ fields are required fields. Whereas, the ‘Gender’ & the ‘Born’ fields are purposely NOT set as required fields. if the ‘Type’ is a Group (or a Band) then I’ll leave the ‘Gender’ & the ‘Born’ fields blank/empty. If this can create an issue with the database let me know, but that is just how I would like my table to be. And I want to try to keep this database as simple as possible

Gotcha, no issues if you already know bands won't have gender or born data. Are you going to mimic MusicBranz database model?

I believe in your post you said that I ought to have a gender table, which I can’t see why? If I wanted to create a Combo Box on ‘Gender’ in a form I would just use a value list since there are only 2-values in the gender Combo Box.

This database is for you, so if you're not planning to use more than two genders, then no problem. The advantage about having a separate table is that you'll have consistency in your data. If you want to add new values, you add it to your table and the combobox automatically includes it. If you decide that instead of female and male, you want it to show woman and man, you go to the same table and modify those records and it will update everything, otherwise you'd have to go through an update query on your artists table and also update the combobox values. I'd rather mess with two records instead of 300+ and the UI change. There's also performance involved, so it's preferable overall. I mentioned that you should take into account more genders because that's a sensitive topic nowadays, but since the database is only for you, there's not much of a problem. Just remember if your database uses a Gender field in some other table, then you'll have some bad duplication going on and you won't follow your determination of building a database with primary and foreign keys.

I'm physically disabled/quadriplegic, I type with a head stick/pointer, & am not a fast typist. In addition, I cannot use a mouse & I need to use the numeric keypad, “mouse keys” to maneuver the cursor around the screen. So, maybe now you may have a clearer understanding regarding the reason why I like using Combo Boxes in any type of Forms. I hope that you can see how any little reduction (or shortcut) in typing, etc. is a tremendous advantage for me.

Allow me to tell you that I'm impressed and inspired by your determination to make technology work for you despite the challenges you face. Your decision to use Combo Boxes in your database design is a great example of how you are finding innovative solutions to make things easier for yourself. It's truly inspiring to see how you are taking control of your situation and making the most of it. I admire your positive attitude and willingness to adapt to these Access norms. MS Access should be more accessible now that I take into account your situation.

Next on the Artist table field list is Genre. Genre is a tricky field but I only want to narrow it down to one genre per artist. Right or wrong.

It's not so much about it being wrong, but about artists doing more than one genre. For instance, take Shakira as an example, she used to make Pop latin music, now she does Reggaeton. Take Snoop Dogg, he would usually make Hip Hop or Rap, but he recently made some Banda music. Now take Michael Jackson, he's considered a Pop artist, the "King", they say. But his music is predominantly Funk, and sometimes disco. So there's actually no right or wrong here. And in this regard, what genre would you consider today as Pop? the same as in the 70s? how about in 10 years? Tricky indeed. Anyway, stick to one genre. It's your rule, just remember that the name of this thread is "Can this small table be broken dow into smaller tables?". So, I suppose you need a Genres table to query from, or store the genre with your artist table. I'm beginning to realize why someone suggested you to join tables with a field in common that is not a primary-foreign key approach. Just keep in mind that if you do not add a genres table, if your database uses a Genre field in some other table, then you'll have some bad duplication going on and you won't follow your determination of building a database with primary and foreign keys.

The Area field This field is the country in which the artist began. The website I’m using also gives you the city/town & the state (or providence) where the artist began, but like I said I want to keep this simple as possible, so I’ll just stick with the countries. I would also want this field to be a Combo Box.

Then you need a countries table that your combobox can query from.

The last field in this tblArtist comes, the Website field.

I get it, you want to store a source website, not the artist's official information. That's OK.

Edger, I know that I have thrown a ton of information that you will need to decipher, which I understand. But if I can I need to tell you a few additional things. I already create a few other small tables like an Area Table, a Genre Table, & a Type Table, but I have two problems with them:

That's an improvement, dj. I'm writing responses as I read your massive post, so it's good for me to finally see that you want to follow your rule of making this a proper normalized database.

1) I don’t know what to do with them

Oh many things, you will join them to your artist table and then you'll use them for your comboboxes. Upon joining them, constraints will be able to be produced and you will have access to things like Referential integrity and the ability to restrict the deletion of records, and allow that if a parent record is deleted, child records will also be deleted. Among other things.

2) I’m repeating field names. For instance, I already have a field name in the tblArtist, called ‘Area’ but I have that same name in the tblArea. Likewise, with the tblGenre & the tblType. All three tables just have two fields in them; an integer field & a short text field with data related to the tables. A. I don’t know which field to assign as the Primary Key. B. I have no idea how to go about creating relation amongst all of these tables.

In your tblArtists (plural, you should store multiple artists here) you will store a field called AreaID, and in tblAreas (plural, you are storing multiple areas), you will have two fields at least: AreaID as primary key and Area (or AreaName, NameOfTheArea, Country, TheArea, TheCountry, ArtistOrigin, TheCountryOfOriginOfTheArtist or however way you want to call it). Then, as I hope you can see, you have to join AreaID from tblAreas, which is a primary key and AreaID from tblArtists, which is a foreign key there. By doing that, you will have a good ol' one-to-many relationship. You create the relationship in the Database Tools tab of the ribbon on top, then click on Relationships.

Furthermore, for just a minute. I’m having great/lots of trouble with the Type field. First, I must tell you in the relationships between my two tables. I opened up the Relational window & chose the tblArtist & the tblType tables. In the tblType, I have just 2-fields, ‘’TypeID’, which is an integer & a ’Type’ (Group or Person). Although, I currently have the TypeID set as the Primary Key, I’m thinking that maybe I ought to change the Primary Key to the Type field instead since that is a unique field also. The relationship I created between these two tables is a one-to-many relationship – one ‘Type’ from the tblType -to- many ‘Type’ in the tblArtist. In my Datasheet Form I created a Combo Box for the ‘Type’ field from the tblType. So, in the dtasht form I’m able to select, “Group” or “Person” in the ‘Type’ field. However, when I open up the tblArtist itself, in the ‘Type’ field it doesn’t display the type (Group or Person),but instead it has he ‘TypeID’. It will either have a “1” if it’s a Group or a “2” if it’s a Person. It’s really messed up & I’m sure it has something to do with how I related the 2-tables.

In tblArtists, you store TypeID field. In tblTypes, you store at least two fields: TypeID (make it primary key) and Type (or NameOfTheType, MyCustomType, TypeOfArtist, whatever). And then in the relationships window, join TypeID from tblTypes, which is a primary key, to TypeID from tblArtists, which is a foreign key there. Do you see the pattern? it's the same you would do with the areas. The rest is how to configure the combobox in your datasheet view. If you can, add your database here and I will help you configure it properly and tell you exactly what to do. It's very late here, so I'll check it out tomorrow. My tomorrow. See ya.

[djclinton15]

Hi Edgar!

I’m sorry for not replying back to you last week. Some issues came up that took my attention away from this project. I must say how impressed as well as appreciative I feel for you for taking out the time to, A) read my massive post detailing the fields within my Artist Table & B) your (in-depth) comment & giving your opinions regarding my fields. I wish now to make a few comments based on your feedback along with one major question to ask you.

Let’s start with the ‘ArtistAbbrev’ field, which as I said could be used as a unique field. Also, (as I mentioned probably in my initial post, I listen to over 260 various artists with abbreviations already created. I have no intention of adding any more artists to my collection. I want to for a second look at your two example bands, “Leon Ground” & “Luna Grande.” Abbreviating these two bands would be relevant easy using my method. *Remember, when abbreviating artists/band I leave out all spaces & vowels, except when the last letter in a name/word is a vowel. So, the abbreviation for, “Leon Ground would be, ‘LnGrnd’ -&- the abbreviation for ”Luna Grande” would be something like, ‘LnaGrnde.’

I am at a loss with the Genre field. A lot of what you said pertaining to this field makes a lot of sense, but I just don’t know what to do with this field. Any suggestions?

What really concerns me is where you said that my tables’ names should be plural. If I were to This would mean that if I were to change all of my that my tables’ names should be plural (which I have seriously considerating doing) in essence, I would be starting from scratch. Because none of my queries, form, & Combo Boxes wouldn’t be any good. Now, I have two questions if I were to start from scratch. 1)In the tblArtists, do I only put the ‘TypeID’ field in this table & none of the other ID fields from the other tables? 2) should field names themselves be singular or plural?

I’m really tempted to create another database (same material), but I’ll try to wait & see when I hear back from you. Dj.

[ArviLaanemets]

What is your final intention with this? Simply a database of all artists, you have any recordings heared/stored/whatever, or a database of all recordings you have/know about/whatever?, which includes performer info too. When later, and the artist info is from where you started currently, then for me it looks like you started at wrong point.

In case your intent is have a full info of your recordings, your database must have (on fly) a structure like:
tblRecordings: RecordingID, RecordingInfo, RecordingCompanyID, ReleaseDate, ...;
tblRecordingDetails: RecordingDetailID, RecordingID, PerformerID, MusicID, ... (MusicID may indicate either a song or something instrumental - I don't know a better english name for it. I put PerformerID here instead of tblRecordings, because I think there exists a possibility, that some recording contain music from several performers. And as same music may be performed by different performers, you must have the performer info linked here, not in table where music info is stored);
tblRecordingCompanies: RecordingCompanyID, RecordingCompany;
tblMusic: MusicID, MusicName, MusicType, MusicGenreID, AuthorID, ... (MusicType may have values e.g. 1 for songs and 2 for instrumental music. About MusicGenreID I'm not sure, belongs it here, or into tblRecordingsDetails. I put it here for case, where different performers can perform e.g. same song in different genre.);
tblMusicGenres: MusicGenreID, MusicGenre;
tblAuthors: AuthorID, AuthorName, ...;
And now we are almost there where you started:
tblPerformers: PerformerID, PerformerName, ... (A performer is either a single artist, or a group);
tblPerformerMembers: PerformerMemberID, PerformerID, ArtistID, ... (Same artist can be a performer him/herself, or be a member of any number of different groups defined as performer);
tblArtists: ArtistID, ArtistName, ...

[Edgar]

Hi, dj,
My general advice is simple:
- To ensure consistency for fields with multiple values, create a separate table. That includes male/female fields. Exception to this is boolean fields like yes/no, true/false, etc.
- It's better to use a consistent naming convention that you understand, rather than one that causes confusion to you. You can keep your names if you like them. Will it confuse other developers? Who knows.

As for the Genre field, you said you wanted to label your Artists with one genre, regardless of anything, so just do that. This is your database, it will be used for yourself and if the rules you have set up for your database make your goal simple, then I encourage you to follow your own rules. It is working for you, so that is amazing.

However, you said you wanted to use table joins and break down your database into smaller tables. If that is the case then here's a possible solution for you:

tblArtist
-- ArtistID
-- ArtistAbbrev
-- ArtistName
-- TypeID <-- fk from tblType
-- GenderID <-- fk from tblGender *
-- Born
-- AreaID <-- fk from tblArea
-- GenreID <-- fk from tblGenres
-- Website

tblType
-- TypeID
-- Type

tblGender *
-- GenderID
-- Gender

tblArea
-- AreaID
-- Area

tblGenre
-- GenreID
-- Genre

* Optional