Techies Corner : Database design query

14 Posts  ·  3 Users
About this group
Ok i know this is a very personal query and i even checked with nikhilkulk but all he says is "If i think its right i shld open" Considering i'm a little desperate for help right now i'm willing to risk this thread getting locked ( my first in age...
Page 1 of 2

Umm yeah basic PHP is pretty simple and there are plenty of sites that'll give you a primer in PHP. I know enough to keep ploughing on for the time being so i'm fine.
Basic SQL, for that matter is pretty simple too except when it comes to creating complex stuff 😁

Commenting on this post has been disabled by the moderator.

That's great 😃 Though I never took effort to learn PHP I do know from reliable sources that it's pretty simple. Maybe you can ask Bijoy Rungta or Deep who am sure would know a lot more about PHP.

Commenting on this post has been disabled by the moderator.

The advantage of above is it will be easier for you to query from the table
.....
Etc etc.. 😃
.................. So this ensures scalability to use a self evident jargon.


This is perfect!! Exactly what i need!! Thank you so much!!

Btw, i have a mysql database and all of this(data entry, viewing and updating) is to be done via PHP.

I'm guessing table creation is a one-time job that i'll be doing manually by running the queries on the server itself. Data entry,i.e. user data, will be done by the user themselves and is basically a Insert/Replace and select.

Oh, and i have no idea how to create the tables with the keys as specified by Oxy. Am a newbie to MYSQL and PHP :D

My offer for dinner still stands. And Oxy gets something nice too
Commenting on this post has been disabled by the moderator.
"Neo2000" Says
The novice/intermediate is user specific and not club specific. There exists just one large happy club with the users defining their proficiency level for that club. I'm thinking we can do away with clubstatusmaster, move clubstatusid into clubid?


No no think I didnt name it properly...lets rename it to ClubMemberStatusId.

ClubMemberStatusMaster :
Description : This contains the various statuses of the members belonging to a club. Basically the domain table for "Intermediate" ,"Novice" etc. The assumption here this is common across all members and is applicable for all clubs. If a particular club does not support these statuses of a member one can have a generic status "Generic" or "NA" .

ClubMemberStatusID --> PK for this table
ClubMemberStatusDesc --> Description

ClubMembershipDet :

Description : This table contains the member ship details for all your members.

MemberId :
ClubID :
ClubMemberStatusID :

Make a composite primary key of the above three fields. And make them as foreign key individually to the master tables.

The advantage of above is it will be easier for you to query from the table ClubMembershipDet on any level.
Eg) With appropriate query you can get the following details :
1) Members belonging to a particular club
2)All the clubs of a particular member
3) All the clubs of a member with a particular status
4)All the members who have a particular status in a particular club

Etc etc.. 😃

Other advantage is since we have separated the master tables from the transaction table you can add more clubs ,change description,status descriptions etc and it wont affect the existing data since they are linked by "Ids" which of course should not be changed. So this ensures scalability to use a self evident jargon.
Commenting on this post has been disabled by the moderator.

OK multiple re-readings confirm this is exactly what i want. But just a Q here w.r.t ClubStatus.

The novice/intermediate is user specific and not club specific. There exists just one large happy club with the users defining their proficiency level for that club. I'm thinking we can do away with clubstatusmaster, move clubstatusid into clubid?

Commenting on this post has been disabled by the moderator.

Oops am sorry. Thought you are familiar with the techie jargon 😃 So do you have anyone out there to whom you can pass on the technical requirements?
What I suggested was just a rough cut. But if you want a solid design that needs to be done iteratively or need a session dedicated for requirement gathering. If you tell us what programming language you are using the relevant guys who have worked on that will help you out. The database design is usually independent of the language. So can help you out with the design of that but as for language specific help and implemntaiton you may need to be more specific ( and also we can learn on the way ..nothing can learning hands on ) and you might target websites dedicated to these kind of stuff.

But what we can do here is come up with a robust database design if you feed us more requirements. The requirements are quite simple anyway 😃

Commenting on this post has been disabled by the moderator.
Wait let us not jump the gun 😃 Let us first concentrate on the database design first and see whether it captures the details you want. Note : This is independent of whatever front end you are using. MYSQL is just a query language.

.....

Girish...!


Thanks man! this is awesome and from what i understand, captures what I want to do.

Er, is it bad that i can barely make out a lot of what's being said?? :(

Whom can I offer a coupla bottles of beer or even a Good Dinner to design and implement this??
Commenting on this post has been disabled by the moderator.

Wait let us not jump the gun 😃 Let us first concentrate on the database design first and see whether it captures the details you want. Note : This is independent of whatever front end you are using. MYSQL is just a query language.

My first "rough cut" design for your requirements from above is the following minimum tables :

MASTER TABLES :

1. MemberMaster :
Description : This table contains all the Member details .

MemberId -- > Make this the primary key .
Rest of the fields of the table should contain whatever details about the member you are capturing. Imp : This should not contain any club membership details. This is just the master table for the members.

2. ClubMaster :
Description : This table all the club details.

ClubID -- > Make this the primary key
Rest of the fields of the table will contain club specific information , Like description,whom to contact etc etc depending on your requirement.

3. ClubStatusMaster :
Description : This contains the various statuses of the club. Basically the domain table for "Intermediate" ,"Novice" etc. The assumption here this is common across all clubs. If you have a requirement where you have club specific statuses then we need to redesign this.

ClubStatusID --> PK for this table
ClubStatusName --> Description

Above are the master tables minimum required for your problem statement.

4. ClubMembershipDet :

Description : This table contains the member ship details for all your members.

MemberId :
ClubID :
ClubStatusID :

Make a composite primary key of the above three fields. And make them as foreign key individually to the master tables.

Availability : --> This would contain the availability of the member. We can make it more fine grained by making a master table of availability statuses and linking them to this table via foreign key. But i leave that to your discretion 😃 If you think the number of options might change you can go ahead and do that. That would make your design more robust to future enhancements.

Rest of the fields you can add to the above table whatever is relevant. This will be your main table for displaying info and which will be frequently changed. We can call it the "transaction" table.

Mind you this is a rough cut design but this should do what you want it do and to a reasonable extent can be enhanced when you undoubtedly get further enhancements.
Is this design approach reasonably clear enough?
Am sure there will be others who can improve on this. 😃

Girish...!

Commenting on this post has been disabled by the moderator.

LOL! ok here we go Full Details :-p

I have

  1. I have a form that consists of 10 drop-down lists that the user chooses to enter his details for a particular club. For simplicity's sake, the first list is the club name, the remaining 9 are miscellaneous details
  2. I have X clubs, more will be added as and when appropriate.
  3. Each user has a unique member-id

I Want :D

  1. A user to be able to view all his membership details of a club in a single page
  2. A user to able to edit his membership details for all his clubs(also preferably on the same page)

E.g.
I belong to the Badminton, Carroms and Chess club with varying levels of proficiency and varying "availability"

On the "My clubs" page, i get to see my details of the different clubs i belong to
Baddy-> Intermediate-> only morning(Availability)
Chess-> Novice-> Only Evening
Carroms ->Expert-> Any time!

If i join a new club, it should become available here

Is it possible to edit these details on the "My Clubs" page? Can i use the value in the database and show that to the user as his pre-set value?

For e.g. when the I view the My Club page, the "availablity" section for all 3 clubs show a drop-down list with the above value pre-selected.

Finally, did i make any sense here??
Commenting on this post has been disabled by the moderator.

I couldn't understand the requirement clearly 😐 Can you explain a little bit clearly? or is there a requirement doc that you can upload here?

if I understand the requirement clearly, I can be of some help

Commenting on this post has been disabled by the moderator.