Basic modeling question

Hi,
I have a simple design problem that I hope someone can help me with.
Assume we have the following tables:
Product
--
ProductID (PK)
Name
Order
--
OrderID (PK)
Date
Attachment
--
AttachmentID
Name
Both table Product and Order can have one or more attachments. If I create
an table called
ProductAttachment
--
ProductID (PK)
AttachmentID (PK)
and one called OrderAttachment I will have a problem where the PK in
"Attachment" will reference both ProductAttachment and OrderAttachment. My
main problem is that we have quite a few tables that can have attachments an
d
I want a design where I don't have to add new columns when new tables that
require attachments are added. I want attachment information to be stored in
one central table.
Any good ideas? Btw, please let me know if you have any tips on sites that
go through database modeling techniques.
Thanks in advance,
MansoManso wrote:
> Hi,
> I have a simple design problem that I hope someone can help me with.
> Assume we have the following tables:
> Product
> --
> ProductID (PK)
> Name
> Order
> --
> OrderID (PK)
> Date
> Attachment
> --
> AttachmentID
> Name
> Both table Product and Order can have one or more attachments. If I create
> an table called
> ProductAttachment
> --
> ProductID (PK)
> AttachmentID (PK)
> and one called OrderAttachment I will have a problem where the PK in
> "Attachment" will reference both ProductAttachment and OrderAttachment. My
> main problem is that we have quite a few tables that can have attachments
and
> I want a design where I don't have to add new columns when new tables that
> require attachments are added. I want attachment information to be stored
in
> one central table.
"I want" is hardly a formal design criteria and is likely to tie you in
all sorts of knots if you aren't careful. I don't necessarily see
anything wrong with the table structure you sketched out. The name
"Attachment" does sound suspiciously like some sort of weakly-typed
property bag arrangement, which could be very nasty - but then I could
be completely mistaken since I've only got your names to go on.
If you think it helps you could create a parent of Products and Orders
and reference that in your many-to-many association table. I'd be very
dubious about the validity of some super entity that encompassed both
Orders and Products however. The fact that it seems to be called for
here is causing me to see the warning letters "OTLT!" written in
flashing red lights over the Attacment table.
http://tonyandrews.blogspot.com/200...ibrary/ms130214(en-US,SQL.90).aspx
--|||David, thanks for taking the time to help me out.
I know this sounds like a "two table solution" but that's not what I'm
trying to do. Attachment is a table used to track file attachments. Many
object can have attachments and I want to try and avoid to create one
attchment table for each type.
A stupid question. If I have one "Attachment" table and two lookup tables
(ProductAttachment/OrderAttachment) RI doesn't allow me to reference both.
How can I design that? I guess that's my question. Or, should I add FK
columns to the Attachment table instead (i.e. ProductID, OrderID) and use
null values and check constraints?
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> "I want" is hardly a formal design criteria and is likely to tie you in
> all sorts of knots if you aren't careful. I don't necessarily see
> anything wrong with the table structure you sketched out. The name
> "Attachment" does sound suspiciously like some sort of weakly-typed
> property bag arrangement, which could be very nasty - but then I could
> be completely mistaken since I've only got your names to go on.
> If you think it helps you could create a parent of Products and Orders
> and reference that in your many-to-many association table. I'd be very
> dubious about the validity of some super entity that encompassed both
> Orders and Products however. The fact that it seems to be called for
> here is causing me to see the warning letters "OTLT!" written in
> flashing red lights over the Attacment table.
> http://tonyandrews.blogspot.com/200...es.htm
l
> But then again, I could still be wrong.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You also newed to learn how to name data elements. "name" by itself is
not a valid data element -- it has to be the name ()date, weight, size,
type, etc.) of SOMETHING in PARTICULAR.
WHY' Are these magically, undefined attachments **logiically** the
same? Could you actually define what they are?
go through database modeling techniques <<
Spend about 6-12 months in a solid college-level course on data
modeling. You are not going to get an education off of the Internet
newsgroups.

Basic Fact Table Design Question

I'm not sure if this is the proper board....but....

I have a sales data from two different cost centers that generate the same data. When setting up my OLAP db, should I:

(1) design two fact tables that would have the same attributes,

(2) one fact table and one dimension table that contained a key field and a sales type field, or

(3) a fact table in which one of the attributes was the sales type field.

Thanks

Do the facts from the different cost centers represent the different instances of the same business process or event? If so, then they should be modeled as a single fact. Another way to approach this is to ask, "Would my users consider these to be the same thing and therefore would expect these to be in the same place in the DW?".

If you model these as a single fact, the cost center would be best handled in a dimension. Sounds like it would have low cardinality. (Is it possible you would ever have more than two cost centers? Many organizations have hundreds or even thousands of cost centers.)

B.

|||Cost center was probably a bad term. The business makes money selling stuff and performing services. They want total sales data and then sales broken between sales and services. The facts are pretty much the same: amount, date, cost of sale/service, etc. I thought a dimension table would work but it's going to be a table with an attribute that has only one of two conditions, plus a key field and a foreign key in the fact table. Seems like a lot of work for a simple A/B field.|||

I think option 2 is the better, but I need to understand some more requirements to decide.

Create a dimension is better, because tomorrow if you have more cost centers, your structure will be more flexible.

But option 3 is not bad...

Regards!

|||

This sounds like a line item fact table. For example, I bring my car in for repairs and what not, I receive a bill (an invoice). On that bill, there will be items for various parts and labor for various services, each with the number of units consumed, a price per unit, and the total cost for that line.

Each item purchased, it's code, name, base price, etc., belongs in a "product" dimension. We may identify each of these products as belonging to a "Parts" or "Service" category, but that catorigation too would belong in the dimension table.

Take a look at the Kimball book, "The Data Warehouse Toolkit", Chapters 2 & 5, for more ideas on this.

Good luck,

B.

basic design question re effective relationship

Hi,

Probably a very simple question but I'm afraid my experience at this is
somewhat limited so...

In a database with a many to one relationship, say orders to customers, is
it preferable to have an UID for a customer and store this in the orders
table rather than the customer name, for example.

CUSTOMERS
++++++++++
UID: Name: Address: Postcode: etc
01 Alpha 12 Acacia Ave HN7YHH
02 Beta 23 Acacia Ave HN6YTH
03 Gamma 28 Acacia Ave HN7UYH

ORDERS
Customer Amount date
Alpha 100 20030823
Alpha 250 20030824
Beta 90 20030825
Alpha 800 20030825
Gamma 1000 20030826

Or is it is better to have:

ORDERS
Customer Amount date
01 100 20030823
01 250 20030824
02 90 20030825
01 800 20030825
03 1000 20030826

Assuming that the second option is the best, how far do I go? I can see how
it would beof benifit to do this for orders and customers where there may be
several hundred thousand orders and hundreds of customers, but for example
would I need to do this for a customer contact database with titles - eg
each title having a UID and using this ID in the customer table?

Thanks for taking the time to read this. Any advice is gratefully received.

Kind thanks

Chris S"Chris Strug" <hotmail@.solace1884.com> wrote...

> Probably a very simple question but I'm afraid my experience at this is
> somewhat limited so...

Hi Chris: Yeah it is a little "basic" but you have to start somewhere...

> In a database with a many to one relationship, say orders to customers, is
> it preferable to have an UID for a customer and store this in the orders
> table rather than the customer name, for example.

Definitely. The CustomerId is the primary key for CUSTOMERS so it would be
the foreign key in the ORDERS table. For a couple of reasons. It is
guaranteed to be unique which the customer name really isn't. While it may
be in the case of a company name (though there can be two "Starbucks"
entries) it most certainly wouldn't be if you were entering people's names.
Also it means you can change/modify a customer name without impacting the
ORDERS table.

> Assuming that the second option is the best, how far do I go? I can see
how
> it would beof benifit to do this for orders and customers where there may
be
> several hundred thousand orders and hundreds of customers, but for example
> would I need to do this for a customer contact database with titles - eg
> each title having a UID and using this ID in the customer table?

Where to draw the line is always open for debate but you wouldn't typically
implement this for "titles" since a person is typically given a single
title. Admittedly it could be a compound title "President & CEO" but would
you want those listed seperately? It introduces problems for using the
database. When printing an envelope somebody has to ask "which of these
titles did you want to use?"

You would use the CustomerId to track multiple addresses perhaps. There is
a common need for a separate shipping address for instance. And the
CustomerId would be used if you were tracking the actual "contacts" like
mailings.

Tom|||Tom,

Thanks for the reply.

The reason I ask is because I have been given a large SQL Server database to
manage. The database handles Containers and their movements in and out of
our facility. The main table has been set up as a many to one link between a
container table and a movement table. The PK in the containers table is an
eleven digit string which acts as a secondary key in the movements table (if
that makes sense!).

Keeping the integrity of the relationship isn't too much of an issue as a
container ID is a code determined by an ISO standard that has all kinds of
checksums in it to ensure it is unique. However, the movements table has
around 250,000 records in it linked to the container table with approx
75,000 records.

My thinking was that by identifying each container with a unique integer ID
and using this as the basis for the PK / SK, this would be quicker and more
efficient - after all I'm sure SQL server can handle an integer value faster
than a string.

Hence my question about titles - in a large customer table, would it make
sense to use a link to a titles table identified with an integer and store
this integer in the customer table as a secondary key? As I said, I think
that storing an integer is more efficient than storing a string?

Thanks for taking the time to read this - your help is greatly appreciated.

Kind thanks

Chris.|||"Chris Strug" <hotmail@.solace1884.com> wrote...

> The reason I ask is because I have been given a large SQL Server database
to
> manage. The database handles Containers and their movements in and out of
> our facility. The main table has been set up as a many to one link between
a
> container table and a movement table. The PK in the containers table is an
> eleven digit string which acts as a secondary key in the movements table
(if
> that makes sense!).

Pretty much... I'm somewhat familiar with the container industry and I'm
working on something related. Is the e-mail address attached to your
message your actual e-mail address, we could chat out of the public eye?

> My thinking was that by identifying each container with a unique integer
ID
> and using this as the basis for the PK / SK, this would be quicker and
more
> efficient - after all I'm sure SQL server can handle an integer value
faster
> than a string.

Personally I wouldn't worry too much about the efficiency of integers vs
strings. It takes whatever time it takes to do whatever it has to do and
you know it is operating faster this year than it was a couple of years ago.
And things will generally operate faster next year again without our direct
intervention.

That said I've been making it a point to establish a unique,
non-informational integer as a primary key in my tables. Again there are a
couple of reasons for it but the primary one is simply that I don't have to
look for a naturally occurring primary key. Additionally I make them unique
to the system. There isn't a pool for the containers and another pool for
the customers, there is simply one pool and everybody gets a key assigned
from it.

> Hence my question about titles - in a large customer table, would it make
> sense to use a link to a titles table identified with an integer and store
> this integer in the customer table as a secondary key? As I said, I think
> that storing an integer is more efficient than storing a string?

If you needed to maintain a seperate list of "titles" for a customer then
yes I would reference a standard integer key. I wouldn't think of it as
"titles" per se but simply some data with a many-to-one relationship. In
your example it is a table of "titles" but you would use the same solution
regardless of what it was. That is why I prefer a non-information, integer
key that the system assigns.

By the way, in most cases no "user" ever sees these keys. They are
meaningless and each one is simply one larger than the previous one. There
is (just about) always a public "code" which is referenced by users. In
your example the user would key in (or select) the (it's called a BIC number
right?) container code.

Oh, the other advantage to using integers in this way is that (in the case
of systems that already exist) the client may have established customer
codes which they (and the customer) have been using for years. Having them
change to a number isn't likely to happen and they can continue to use the
codes they are familiar with. The code is looked up, the integer id is
determined and the system uses it internally for all other queries.

Tom|||Tom,

Thanks for the taking the time to reply.

I'm a bit tied up at the moment (deadlines 'n all) but yes, the hotmail
account is valid - just swap the domain with the username. Please feel free
to drop me a line.

I'll come back to your post in a bit, but in the meantime, thanks again for
your help.

Cheers

Chris.sql

Basic Design Question Currency Dimension or New Measure

Hi All,
I am in the middle of a decision and I would like to ask experts for their
ideas.
I have Sales Reporting System on MSSQL 2000 + AS + RS
So far I have used only USD as reporting currency, now I am going to add
EURO and Local Currencies. My Current Model has "Sales", "Performance%" etc
as measures and Items,Countries etc as dimensions.
The question is how should I add new currencies to the system ?
As new Measures Sales_EURO?
or
As adding them to the fact table as rows keeping only one Sales measure and
adding a Currency dimension ?
I have around 2 million record in my fact table and data is accessed via
Reporting Services reports and via an application using OWC Pivot Table.
Thanks in advance?
erdalErdal,
Check out Adventure Works Enterprise Analysis services sample. I believe it
solves your problem via a currency dimension.
"erdal akbulut" <erdalim21@.yahoo.com> wrote in message
news:eJWeL32cGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I am in the middle of a decision and I would like to ask experts for their
> ideas.
> I have Sales Reporting System on MSSQL 2000 + AS + RS
> So far I have used only USD as reporting currency, now I am going to add
> EURO and Local Currencies. My Current Model has "Sales", "Performance%"
> etc
> as measures and Items,Countries etc as dimensions.
> The question is how should I add new currencies to the system ?
> As new Measures Sales_EURO?
> or
> As adding them to the fact table as rows keeping only one Sales measure
> and
> adding a Currency dimension ?
> I have around 2 million record in my fact table and data is accessed via
> Reporting Services reports and via an application using OWC Pivot Table.
> Thanks in advance?
> erdal
>
>
>|||Tim Thanks for you reply.
Unfortunately I dont have SSAS 2005 installed, think that sample comes with
SSAS 2005. Do you know is there any documentation for that project that I
can use without installing the application itself?
erdal
"Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> wrote in message
news:eD1tkPdeGHA.380@.TK2MSFTNGP04.phx.gbl...
> Erdal,
> Check out Adventure Works Enterprise Analysis services sample. I believe
it
> solves your problem via a currency dimension.
> "erdal akbulut" <erdalim21@.yahoo.com> wrote in message
> news:eJWeL32cGHA.4576@.TK2MSFTNGP05.phx.gbl...
their[vbcol=seagreen]
>|||Not that I'm aware of. Sorry.
-Tim
"erdal akbulut" <erdalim21@.yahoo.com> wrote in message
news:uE7najleGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Tim Thanks for you reply.
> Unfortunately I dont have SSAS 2005 installed, think that sample comes
> with
> SSAS 2005. Do you know is there any documentation for that project that I
> can use without installing the application itself?
> erdal
> "Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> wrote in message
> news:eD1tkPdeGHA.380@.TK2MSFTNGP04.phx.gbl...
> it
> their
>

basic design question

i am a beginner and any response will help me a lot.

i have 20 types of products,
each product has about 40 fields, most of them are common fileds for all products, but some of them are unique.

the number of the unique fields of all the product types is 75.

the question:
Should i build one products table with 75 fields and leave nulls where a field is not property of a product type?
Or should i build a products table for the 30 common fields and a 20 different tables for each product type extra fields?
Or just build 20 different tables each product type?

other ideas are welcome

you can use Named-pair design technique that is very comon in Software as Service application where we have to maintained the list of customer and some customer can have attribtues that are uncomon to other.

create a table called ProductAttributs that can have column like

1- ProductAttributeID,Attribute Name , Data Type ,IsNull

then Create another table called Product Attribute Value that has column

1-ProductID

2-ProductAttribute ID

3- Value

and you have a base table Product which contains the common fields only

so by this first you can create a set of advance product attributes in Product Attribute Table

then bind your product table with Product Advance attributes in product attribute value so by this you can manage dynamic Product attributes that are not comon in ever Product.

|||

hi., please help me, i am a student, i have basic idea on webprogramming specially ASP.NET, actually im using Visual Web Developer.,

i have a new "complicated" project. i really need help.

1st. the data base, this is the diagram of the data, it is not the actual form but its more like this:

if i will do it in the form of access, it will look like this.

LinkFromLinkToexisting1depleted1fots_brae1cdma_fots1 1111AngelesArayat16800AngelesBalanga27000AngelesBalibago63000AngelesClark16000AngelesDinalupihan0000AngelesHermosa0000AngelesMacabebe64000AngelesMagalang16800AngelesOrani0000AngelesSFP7312600AntipoloAntipolo RSU16000AntipoloTanay42000BacoorBacoor RSU166300BacoorCavite City16000BacoorDasmarinas152100BacoorImus63000BacoorKawit16000BaguioBaguio RLU/ELU1111BaguioLa Trinidad1111BalagtasAngat1111BalagtasAngeles1111BalagtasBocaue1111BalagtasCabanatuan1111BalagtasGuiguinto1111BalagtasIba1111BalagtasMalolos1111BalagtasMarilao1111BalagtasMeycauayan1111BalagtasNorzagaray1111BalagtasPandi1111BalagtasSan Jose DM1111BalagtasSta. Maria1111BalagtasTarlac1111BatangasLemery1111BatangasLipa1111BatangasMamburao1111BatangasSan Jose1111BatangasSto. Tomas1111BinalonanAsingan1111BinalonanBaguio1111BinalonanBalagtas1111BinalonanBalungao1111BinalonanDagupan1111BinalonanLaoag1111BinalonanLingayen1111BinalonanMt. Kabuyao RS1111BinalonanPozzorubio1111BinalonanRosales1111BinalonanSan Carlos1111BinalonanSan Isidro RS1111BinalonanSan Manuel1111BinalonanSan Nicolas1111BinalonanSan Quintin1111BinalonanSantiago1111BinalonanSFU1111BinalonanSison1111BinalonanSta. Maria1111BinalonanTayug1111BinalonanTuguegarao1111BinalonanUmingan1111BinalonanUrdaneta1111BinalonanVigan1111BinalonanVillasis1111

if you would look on the table and the diagram, there are just 2 lavel in the table which is in the diagram has 3 level.

thisis the story about that. the table also has 3 level, look on the Boldwords, if you would look on the many "angeles" there is also not so farbeneath a single "angles" but this time, he is under "balagtas", sothis is the levels, "Balagtas --> Angeles--> (bold names underangeles)" how to know the other names with same levels,

@. lookfor names that are inputed in many times in the "LinkFrom" header thenlook for the same name in the "LinkTo" header. so just look closely inthe table so you can under stand the format.

so this is the question, how can i design my database from access?? what will it look like?

after the database, this is the story for how should the web page operate.

@. assumption: that there will be 3 dropdown menu, each menu correspond to each level.

sofor example, if i choose an item in the level 3 dropdown menu, it willshow a table or info (if you would look in the table above, it will bethe numbers' "1's") for:

1. link from level 2 to level 3.
2. link from level 1 to level 2

so to make it short, will should detect he's original link were ever he was link to.

so if i select a value from the level 2 dropdown, it will only show the link from level 1 to level 2

if select a value from level 3 it will show link from level 2 to level 3 and level 1 tot level 2.

it should trace were he is connected so i think the real key here is the design of the database.

actuallythe table i show to you is the real table but i cut it to make itshort. so there are still some 3 level layer out there in the sameformation as the "Balagtas --> Angeles --> under angeles" intwo columns style.

i hope someone can help me tothis,...please. this day is friday here in the philippines, i willhave no class tomorow, only here in the school were i can work and openthis forum. i hope in monday there will be an answer. thanks in advanceto any person.

please feel free to ask it you want to know any clarification to my post.

SALAMAT PO...

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself asp.net with non code-generating tools. I am stuck on some basic DB design thoughts and would appreciate some feedback.

Right now I have pages that let me input some of the initial data like Users, Teams and Game Schedules. I need to be able to record whether a user has paid their weekly entrance fee but I don't know which table to store this info even what datatype would be the best solution.

Here is the table design I have so farhttp://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the users tables would be the asp.net 2.0 mambership ones which are too many to list...

Thoughts please. TIA

I would suggest the following as it pertains to the business logic of your task.

Create a table for the "week" with weekId and date (no time reqd). This is a central table that links most of the others together

Create a table formoney input or "Subscription" with a link to your dateId and user

You have a table formoney output - winnings which links to dateId.

You might also need to store more user details - maybe more on address, more phone numbers etc.

|||You are in the right direction about your table design while the previous advice not about table design, you can use Microsoft provided membership for your users and just add DRI(declarative referential integrity) to team, schedules and subscription fee tables, money you need to use Decimal or Numeric because you can set precision and scale. Hope this helps.

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIAWell, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have keys
and relations.
"fwells11@.hotmail.com" wrote:
> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>sql

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIAWell, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have key
s
and relations.
"fwells11@.hotmail.com" wrote:

> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>

Basic DB Design question

I have decided to build a Football Pool app as a way of teaching myself
asp.net with non code-generating tools. I am stuck on some basic DB
design though and would appreciate some feedback.
Right now I have pages that let me input some of the initial data like
Users, Teams and Game Schedules. I need to be able to record whether a
user has paid their weekly entrance fee but I don't know which table to
store this info even what datatype would be the best solution.
Here is the table design I have so far
http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
users tables would be the asp.net 2.0 membership ones which are too
many to list...
Thoughts please. TIA
Well, seems like you could do it several differant ways. One would be to
have a user table with an extra field, "Paid". Then when on Monday night
around 1 am schedule a job to clear "Paid" field in the user table. Or if
you want to keep track of each week and partiapation, and add a field for
each week. But then again, you can have a Paid table with user name and
week(as the key). You see that is the Great thing about about relational
database's, you can be as creative as you want. Just make sure you have keys
and relations.
"fwells11@.hotmail.com" wrote:

> I have decided to build a Football Pool app as a way of teaching myself
> asp.net with non code-generating tools. I am stuck on some basic DB
> design though and would appreciate some feedback.
> Right now I have pages that let me input some of the initial data like
> Users, Teams and Game Schedules. I need to be able to record whether a
> user has paid their weekly entrance fee but I don't know which table to
> store this info even what datatype would be the best solution.
> Here is the table design I have so far
> http://www.insecurepc.com/FootballPoolDBDesign.jpg Before you ask, the
> users tables would be the asp.net 2.0 membership ones which are too
> many to list...
> Thoughts please. TIA
>

Balancing scalability with availability

We are beginning to design a new application with SQL Server 2005. Our current production environment is slated to be two SQL Server 2005 machines with the databases residing on an EMC SAN. We have requirements to both have automatic failover between servers for availability and also be able to balance the load over two hot servers for scalability.

Can anyone point me in the right direction for things I need to consider in order to be able to implement both of these requirements? Can I implement database mirroring (for failover) and transactional replication (for balancing) given the hardware configuration I'v mentioned? Is more information needed? Where should I turn next?

I am coming off a mainly Oracle background for the last ten or so years with a smattering of SQL Server mixed in. I've tried to hit the ground running on this project, but sometimes find myself hitting the wall running instead.

Thanks,

Larry

Automatic failover can be accomplished with Database Mirroring, and clustering. With both of these technologies the mirrored server or the failover node will be offline so you can't access the databases on these mirrored servers - hence they won't deliver load balancing. For load balancing you could use replication assuming all the DML occurs on one server. Replication will not provide client failover however.

-- Hilary Cotter

|||

That is not correct.

The instance hosting the mirror database is NOT offline. It is online and running otherwise transactions would not be able to reach it. The mirror database is inaccessible, because it is in a recovering state.

As far as clustering goes, the secondary node is also not offline. It is online and functioning, otherwise, there would be no way to failover to the node. There is only one copy of the database within a cluster, so you can't use the secondary node to execute anything.

The combination that is being asked for - automatic failover along with scalability is not possible. Failover clustering will automatically failover, but is not a scalability solution. Database Mirroring running in high availability operating mode will automatically failover, but is not a scalability solution. However, if your applications are not coded to intercept the disconnect and then reconnect to the cluster, it doesn't matter if the cluster automatically fails over, because only those applications forming new connections will find it. If your applications are not coded to utilize the new connection library in ADO.NET that shipped with VS 2005, then your applications are not going to automatically failover to the mirror when it gets promoted.

Replication is your closest bet to getting both fault tolerance as well as scalability. If your applications are built with a connection library that can detect that the server they are connected to is offline and then reform a connection to a secondary server and continue issuing transactions, then you can accomplish the basic goals. Your application then handles the automatic failover while the replication engine keeps everything synchronized to allow scalability. The basic options here are transactional with queued updating subscribers and merge. I would NOT configure a bi-directional or peer-to-peer architecture unless your are going to have full control over your applications and can guarantee that you can never have a conflict generated.

bad table design

A developer has just gave me a few tables table to put
into production, with all character columns defined as
varchar(255), null.
As I am absolutely positive that the majority of these
columns will not be storing this amount of data in each
of the columns, as some of them are code columns, types,
and one description field.
After asking him to review all of the columns and the
maximum estimated data stored in each one, he said that
it shouldn't make any difference because A) they're
varchar and B) they're defined as null.
What can I say to him to make him do the needful?
Thanks,
MarcusMarcus
Well, variablle-length characters ( as in your case) is stored 1 byte per
character.Declared but unused characters don't consume storage.
On the other hand I'll be conserned about using NULL's when you will be
querieng the tables.
Using IS NULL/NOT NULL clause will not allow Query Optimizer to use an index
and respectively your query will suffer from perfomance hit.
Also consider using DEFAULT values in those columns.
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus|||Pick up one of Joe Celko's post from here regarding how important it is to
get the database design right and where he state that he almost never have
to use NULL. If he question Joe's credibility, search for instance Amazon of
the books he has been writing (or go to www.celko.com).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus|||Right. I've brought that up also and he's come back and
said that the table is for importing data only and it
shouldn't matter.
You're thoughts?
Thanks!
Marcus
>--Original Message--
>Marcus
>Well, variablle-length characters ( as in your case) is
stored 1 byte per
>character.Declared but unused characters don't consume
storage.
>On the other hand I'll be conserned about using NULL's
when you will be
>querieng the tables.
>Using IS NULL/NOT NULL clause will not allow Query
Optimizer to use an index
>and respectively your query will suffer from perfomance
hit.
>Also consider using DEFAULT values in those columns.
>
>"Marcus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
types,
>
>.
>|||Marcus
Look, we can discuss a lot on this topic.
In outer-join operation you should carefully account for NULL's values that
are generated to preserve rows that don't have a match in the table being
joined.
Also dealing with NULL's adding complexity to the storage engine because SQL
Server keeps a special bitmap in every row to indicate which nullable
columns actually are NULL. SQL Server must decode the bitmap for every row
accessed.
Again I'd recommed you/your opponent to make all nullable columns as NOT
NULL and DEFAULT values.
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8c2301c404f2$255e24a0$a501280a@.phx.gbl...
> Right. I've brought that up also and he's come back and
> said that the table is for importing data only and it
> shouldn't matter.
> You're thoughts?
> Thanks!
> Marcus
> stored 1 byte per
> storage.
> when you will be
> Optimizer to use an index
> hit.
> message
> types,|||"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
Two aspects :
varchar(255), there is nothing wrong defining a varchar
type with a number of characters. Varchar(20) and varchar(255)
both occupie the same number of bytes if the strings are the same.
255 doesn't look like a 'natural' number, it is typical a number
which comes out of the head of a 'computer' oriented mind.
(80, 100, 200, 400 are more natural). But if the data comes
from another 'computer' system 255 could be the right number.
Null,
Never prefered and should be avoided. But for only importing
data (on the way to another table) and for text holding fields
on which no selection and no join is done a 'logical' choice.
If the text is human generated and a 'non' text is one of
the posibilities, I think that null is ok.
(When indexed, joined, used in a where clause one should
avoid a null).
Also concatenating with <null> strings should be avoided,
because this is handled differently in different databases.
(One could choose as wel for an empty string instead of
a null, this is supported in SQL-server, but be aware there
are RDBMSses which consider an empty string equal to
a Null string.)
I would not know of a default in a text string which is a
good replacement for a null string.
(Considering other factors as usage by programs, utilities
and other databases. Empty string is not supported
in all databases.
Using a single character as a default might conflict
with a 'real' single character even if it is a space.)
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
vvvvvvvvvv
Does anybody know of a good 'default' for character strings
instead of using a null ?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ben brugman

> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus

Bad Design?

Table A
[ID] [SalesmanEmail] [SalesmanName]
Table B
[ID] [QuoteNo] [SalesmanID]
Table C
[ID] [Product] [QuoteNo]

Program A creates the records for Table C. In the process it reads from Table A and Table B.

Program B reads record from Table C. Program B now needs [SalesmanEmail] field.

The suggestion is to add [SalesmanEmail] to Table C. Is this good or bad design?Select TableA.SalesmanEmail From TableA Where TableA.ID In (Select Distinct TableB.SalesmanID From TableB Where TableB.ID = whatever);|||That's how I suggested we do it, however the "senior" programmer was the one who asked me to write out SalesmanEmail directly into Table C.

I tried to explain to him that if there were ever any modifications to SalesmanEmail in Table A or to SalesManID in Table B, every record I wrote in Table C would now be invalid because we wrote it to a column instead of doing a lookup, but he still didn't get what I meant. And his method also wastes DB space by storing the same field in multiple tables.|||The suggestion to put the salesman's email address into Table C violates the Second Normal Form of database normalization. It belongs solely in Table A.

Terri|||:: Table C violates the Second Normal Form of database normalization.

Is that a misdeameanor or felony? or does the SQL cop just write you a ticket?

j/k. :)

Yeah, I just gave a query example how you don't need to add a new field if it's already in another.|||But he would probably be more comforted to see:

SELECT
 TableC.Product,
 TableC.QuoteNo,
 TableA.SalesmanName,
 TableA.SalesmanEmail
FROM
 TableC
INNER JOIN
 TableB ON TableB.QuoteNo = TableC.QuoteNo
INNER JOIN
 TableA ON TableA.SalesmanID = TableB.SalesmanID

;-)

Terri
PS - I think the penalty is pocket protector removal