Database Discussion Boards

View All Threads

First Prev Next

SQL 2008 Data Base Table Design suggestion

VishwaKL4-Dec-12 23:36

VishwaKL

4-Dec-12 23:36

Hi Guys,

I have a problem to create data base.

My requirement is like

there are 4 different sites are there and every site has a common checklist to be checked and sites also have extra check list to checked with common check list, for this how i should create data base table.?

Thanks in advance
Vishwa

Re: SQL 2008 Data Base Table Design suggestion

Simon_Whale4-Dec-12 23:43

Simon_Whale

4-Dec-12 23:43

From you brief description;

I am assuming that you are using a central database.

I see that you would need to create a

site table (used to store the sites that will use the database)
Checklist Template (list of things to check off)
Checklist Completed table (a join table that links the site with the completed items from the template table)

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: SQL 2008 Data Base Table Design suggestion

VishwaKL4-Dec-12 23:47

VishwaKL

4-Dec-12 23:47

Thanks Simon

that extra check list are not common to all sites so. how can i relate those points

like which is required which is not for particular Site?

Re: SQL 2008 Data Base Table Design suggestion

Simon_Whale4-Dec-12 23:51

Simon_Whale

4-Dec-12 23:51

well in the check list completed table (more than likely not a good name) you could record the todo item and a datetime completed? and the site that it is linked to.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Re: SQL 2008 Data Base Table Design suggestion

PIEBALDconsult5-Dec-12 3:56

PIEBALDconsult

5-Dec-12 3:56

Your description isn't clear enough, so here's a design that could be used for some interpretations of what you wrote.

0) There are several sites.
1) A check list has several check list items.
2) Each site has a check list with a subset of all available check list items.
3) Each check list item may be on several sites' check lists.

This is a classic many-to-many relationship and the typical implementation is to have (at least) three tables:

Site: ID, Name, etc.
Item: ID, Name, etc.
SiteList: SiteID, ItemID (usually no other data here, but maybe in some cases)

Another schema would allow for defining lists and assigning them to sites:

Site: ID, Name, etc.
Item: ID, Name, etc.
List: ID, Name, etc.
ListItem: ListID, ItemID (usually no other data here, but maybe in some cases)
SiteList: SiteID, ListID (usually no other data here, but maybe in some cases)

This second way may be approriate for your needs, you can define a common list and custom lists and assign multiple lists to the sites. Getting a complete list of all items for a site is simple as well.

Last Visit: 31-Dec-99 18:00 Last Update: 3-May-24 21:21

Refresh

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.