March 11, 2012 at 6:01 am
hello there! i'm trying to make a database containing information about bands. the problem i'm facing is this--
every band has multiple members.these members have their own instruments,working status and name.so i want to make two tables,one for band's info and stuffs,other one containing individual band member's info.but the number of members vary from band to band.is there any way that i can apply sub-attribute like characteristic inside a particular attribute? i mean like inside the members table,i can put instrument and working status under each member's name?please help
March 11, 2012 at 8:12 am
Here is something (rather simple) to start you off
We create two (2) tables, the first Bands to hold basic information on the band itself. Nothing about an individual member.
CREATE TABLE [dbo].[Bands](
[BandId] [varchar](10) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Music] [varchar](10) NOT NULL,
[Contact] [varchar](100) NOT NULL,
CONSTRAINT [PK_Bands] PRIMARY KEY CLUSTERED
(
[BandId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Bands] WITH CHECK ADD CONSTRAINT [FK_Bands_Bands] FOREIGN KEY([BandId])
REFERENCES [dbo].[Bands] ([BandId])
GO
ALTER TABLE [dbo].[Bands] CHECK CONSTRAINT [FK_Bands_Bands]
GO
Now create a table which will retain information about each memeber of the band. Linking each member to their band, through the foreign key
CREATE TABLE [dbo].[BandMemebers](
[BandID] [varchar](10) NOT NULL,
[First Name] [varchar](20) NULL,
[LastName] [varchar](50) NULL,
[Instrument] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[BandMemebers] WITH CHECK ADD CONSTRAINT [FK__BandMemeb__BandI__4CA06362] FOREIGN KEY([BandID])
REFERENCES [dbo].[Bands] ([BandId])
GO
ALTER TABLE [dbo].[BandMemebers] CHECK CONSTRAINT [FK__BandMemeb__BandI__4CA06362]
GO
Hope this gets you started ...
March 11, 2012 at 8:19 am
I wonder if the following scenario should be implemented, too:
One person can play different instruments in either the same band or in different bands (e.g. Bass guitar and Acoustic guitar).
March 11, 2012 at 8:45 am
LutzM (3/11/2012)
I wonder if the following scenario should be implemented, too:One person can play different instruments in either the same band or in different bands (e.g. Bass guitar and Acoustic guitar).
Alternatively the table BandMember could be defined as, for a second instrument, or again for a 3rd instrument.
CREATE TABLE [dbo].[BandMemebers](
[BandID] [varchar](10) NOT NULL,
[First Name] [varchar](20) NULL,
[LastName] [varchar](50) NULL,
[Instrument] [varchar](20) NOT NULL,
[2ndInstrument] [varchar](20) NuLL
) ON [PRIMARY]
In addition, an individual could be a member of more than one band, and hence require an entry in the table BandMembers for each band. And in that entry the primary [instrument] may or may not be the same in each band. And in the design you adopt, be aware of this.
March 11, 2012 at 9:11 am
yes i think im close to solving my problem.thank u all
March 11, 2012 at 9:22 am
lamiajoyee (3/11/2012)
yes i think im close to solving my problem.thank u all
And thank you for letting us know that in some way we assisted you.
And now a request for you. When you do arrive at your final design, please post it here, along with the reason(s) you did what you did, so as to help others faced with similar problems.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply