Table Design (1 Viewer)

[ZionRootical]

Hi Again,
Thanks to Jdraw, this last week i've read up a lot about Normalisation, at least 3 different ways of laying tables etc.

I've used a lot of paper and now using excel to create tables etc. just saves paper and ink, and looks neater

The Problem i have after sitting down i've discovered a lot more info i want to record into my Reggae Database,
1st Question, should i create every thing in a single Access Database "Reggae" or split the tables into groups and have multi Access Database's so the relationships would be easer to manage, coz most tables are related M:M
ie,
Reggae Personel, (Just Reggae Performer Info)
Reggae Companies, (Record Shops, Record Labels, Pressing Plants, Radio Stations)

as i've so far ended up with about 65 tables and still finding more, this is without adding link tables to M:M Relationship

2nd Question, this applies to a lot of tables.
I had Singer, Deejay, Producer, Sound Engineer, Mixing Engineer, Label Owner all in a table name "Personal"
and had a "Role" table showing the roles, my trouble is they all have multi roles over their Careers

so i split them up into individual tables, but now found the tables contain the same field names which will consist of same type of info
tblSinger_______________tblDeeJay_____tblProducer_____tblStudi oOwner___tblLabeOwner
SingerID________________DeejayID
SingerTitle_______________DeeJayTitle
SingerFirstName__________DeejayFirstName
SingerLastName
SingerNickName
SingerCareerStarted
(These Tables are their Reggae Names not Real/Birth Names that's in another table)
which is the best practice of course i won't store the same Singer in Producer or Label Owner etc
In Personal Table i'll have 3000+ so splitting them up in their main category would be easy

Thanks for the imput

 

[namliam]

N:M (or M:M as you call it) SHOULD NOT exist in a relational database, instead you should make a junction table that resolves this N:M to a N:1 and 1:M relationships

Having 65 tables is quite a few, however there shouldnt be a problem having 6500 tables in one database assuming your design is proper....

Instead of making 3000+ tables, simply make one table that will contain most key ifnormation like Name/type/etc and add a column that says date/from etc...

 

[ZionRootical]

Thanks Namliam

Sorry My Bad for using M:M i knew i had to use a linking table

so just to double clarify u suggest scrapping the tables about a persons Roles: tblSinger, tblDeejay, tblProducer

add them back (My Original Thought) in tblPersonal with a another tblRole
and list the role in one column ie..
RoleID Role
01 Singer
02 Deejay
03 Producer
04 Label Owner

also i did originally have a tblNickName, listing all nicknames

Thanks, again

 

[namliam]

Yeah the role table makes sence, you may even want to add stuff in there like StartDate-EndDate-Nickname-etc that may be related directly to the role
So I would probably make a table, tblPeople that holds everything about the natural person himself
Then have a table tblRole, that manages the 1 person to many Roles where you can administrate the many roles, different nicknames that belong to the role...
Unless you need to be able to manage a person having several nicknames to one role in which case you do need the tblNickName

 

[ZionRootical]

i've just Zip my Excel Sheet where i'm designing the database

please have a look, "Table Layout Current" is the one i'm using at moment, the "Table" and "Tables" are old ones I changed around etc, don't take any notice of "Relationship" something i was toying with before i made some changes. I haven't done anything since i posted originally post.

I thought under 3rd Rule i wasn't allowed any Columns with the same Data
ie DateStarted and DateFinished in any one table, this was causing a bit of headache.

thanks for your time and comments in advance

 

[namliam]

DateStarted and DateFinished are not the same data, they are different...

Same data would be ... lets say a person started the role of DJ 3 different times with 3 different nicknames.... You would need a structure like:
DateStarted1 - DateFinished1 - Nick1 - DateStarted2 - DateFinished2 - Nick2 - DateStarted3 - DateFinished3 - Nick3
to accomidate it...

Instead you dont want same data:
ID - DateStarted - DateFinished - Nick
And you simply record seperate records for it

Global tip, any tables always prefix them with tbl... then for any query's always qry etc.... This will help you in the future to keep them seperated quite easily.

Now as to your design, no need to do i.e. CountryOfDeath, CountryOfContact, CountryOfBirth, instead just call it Country assuming you put it in seperate tables offcourse.
The Birth and Death table will (likely ) have a 1:1 or 1:0 relationship, which if you choose to should allow you to merge the two

By removing the "Table name" from the column names it becomes quite appearent that your singer/deejay/dubpout tables are basicaly one and the same and you dont need the seperation.
Same goes for SountSyatem/DiscothequeSound/etc...

Dont know if "NumberOfChildren" belongs in the family table... Family tend to change and children is basicaly a "Count(*)" from the children table (which you dont have) thus could possibly be a calculated value if you are intrested in actually keeping track of the individual children names and possibly who they "belong to", i.e. Daddy X and Mommy Y vs Daddy X and Mommy Z vs Daddy A and Mommy Y

If you have a problem with so many lookup tables (12AMatrix, 12BMatrix, CDSingle, etc)
you can choose to have only one table and have a third column to determine the type, plus the ID will ultimately be unique so joining it back to the actual tables shouldnt be any issue

Also I would think about the "Year of" columns, once you abstract dates to years there is no going back... perhaps you want to change those to actual dates like instead of YearGroupStarted, perhaps you know the month or even the exact day which is something you lose if you record only the year.

 

[ZionRootical]

Thanks Namliam, for your time, i've have a jiggle and reconfigure,

i will write a narrative paragraph as it seems to help,

Saddly Reggae Artist change their name very regular, and labels print mis spelt names so a Singer could sing under 3 names spelt another 3 ways whom also DeeJay under another Alto Ego whom have 2 or 3 nicknames

Thanks anyway, still a long way off, before i've finished designing

 

[ZionRootical]

As for the Family i'm only counted the Family members whom become Reggae Singers Producers etc

as for the children count some old artitist like Bob Marley have 10 children and above

5 of Bob's children Sing

prob pointless sorry be good if you liked Reggae lol thanks again