Database Errors

I'm trying to script a SQL server 7 database using the
tool in EM. Every time I try I get error 220 Arithmetic
overflow error for data type smallint, value = 65535.
It's quite clearly correct in that it is out of range,
what I don't understand is why and what I can do to
resolve this error. After I found this out I discovered
that all the databases on this server give the same error
when I try to look at their properties using enterprise
manager.
Does anyone have any ideas what is causing this and how I
can prevent it
replies please to elwoosAThotmailDOTcom
thanksTry to script the objects seperately and see where this error occurs.
Other way to scritping:
http://www.nigelrivett.net/DMOScripting.html
http://www.nigelrivett.net/DMOScriptAllDatabases.html
"elwoos" wrote:

> I'm trying to script a SQL server 7 database using the
> tool in EM. Every time I try I get error 220 Arithmetic
> overflow error for data type smallint, value = 65535.
> It's quite clearly correct in that it is out of range,
> what I don't understand is why and what I can do to
> resolve this error. After I found this out I discovered
> that all the databases on this server give the same error
> when I try to look at their properties using enterprise
> manager.
> Does anyone have any ideas what is causing this and how I
> can prevent it
> replies please to elwoosAThotmailDOTcom
> thanks
>

Database Errors

I'm trying to script a SQL server 7 database using the
tool in EM. Every time I try I get error 220 Arithmetic
overflow error for data type smallint, value = 65535.
It's quite clearly correct in that it is out of range,
what I don't understand is why and what I can do to
resolve this error. After I found this out I discovered
that all the databases on this server give the same error
when I try to look at their properties using enterprise
manager.
Does anyone have any ideas what is causing this and how I
can prevent it
replies please to elwoosAThotmailDOTcom
thanks
Try to script the objects seperately and see where this error occurs.
Other way to scritping:
http://www.nigelrivett.net/DMOScripting.html
http://www.nigelrivett.net/DMOScriptAllDatabases.html
"elwoos" wrote:

> I'm trying to script a SQL server 7 database using the
> tool in EM. Every time I try I get error 220 Arithmetic
> overflow error for data type smallint, value = 65535.
> It's quite clearly correct in that it is out of range,
> what I don't understand is why and what I can do to
> resolve this error. After I found this out I discovered
> that all the databases on this server give the same error
> when I try to look at their properties using enterprise
> manager.
> Does anyone have any ideas what is causing this and how I
> can prevent it
> replies please to elwoosAThotmailDOTcom
> thanks
>

Database design question. I am close ;)

Lets say I am selling parts for cars, trucks, suvs, vans, etc.

I want a user to search for a part by selecting a vehicle type, then a vehicle make, and then a vehicle model.

So

Vehicle Makes have to be related to Vehicle Types (I.E: Land Rover doesn't make a car) and vehicle models need to be related to vehicle make (I.E Toyato doesn't make a mustang Ford does).

So I am okay with those relationships individually but what is confusing me is how to ensure that a Mustang doesn't show up as a Ford Car.

Lets say I have:

vehicle_type
vehicle_type_cd
vehicle_type_desc

vehicle_make

vehicle_make_cd
vehicle_make_desc

vehicle_model
vehicle_model_cd
vehicle_model_desc
Then I might have an intersect between vehicle_type and vehicle_make because certain makes don't have certain types of vehicles. BMW doesn't have a truck, Lexus doesn't have a truck, so when a user selects truck as a make lexus nad BMW shouldn't show up in the drop down.
Vehicle_type_make
Vehicle_type_make_key
Vehicle_type_cd
Vehicle_make_cd

I might have the same relationship between make and model:

Vehicle_make_model
Vehicle_make_model_key
Vehicle_make_cd
Vehicle_model_cd

With this database design, if I were running a SQL statement to find criteria that matched "Truck" and "Ford" to populate the model dropdown I wouldn't know whether mustang was a truck or car.
What would be the best way to design this.

I hope you can help.

Thank you so much

Given the table structures you have provided, this query should return you the results you are looking for:

SELECT
*
FROM
Vehicle_Model VML
INNER JOIN
Vehicle_Make_Model VMM ON VML.Vehicle_Model_Cd = VMM.Model_Cd
INNER JOIN
Vehicle_Type_Make VTM ON VMM.Make_Cd = VTM.Vehicle_Make_Cd
INNER JOIN
Vehicle_Type VT on VTM.Vehicle_Type_Cd = VT.Vehicle_Type_Cd
INNER JOIN
Vehicle_Make VMK ON VMM.Vehicle_Make_Cd = VMK.Vehicle_Make_Cd
WHERE
VT.Vehicle_Type_Desc = 'Truck' AND
VMK.Vehicle_Make_Desc = 'Ford'

|||Woah. I wasn't thinking to clearly was I? Thanks for knocking some sense in to me.
Umm, okay what do you think about that design is it okay?|||This is the structure you've laid out:

vehicle_type
vehicle_type_cd
vehicle_type_desc

vehicle_make
vehicle_make_cd
vehicle_make_desc

vehicle_model
vehicle_model_cd
vehicle_model_desc
Vehicle_type_make
Vehicle_type_make_key
Vehicle_type_cd
Vehicle_make_cd

Vehicle_make_model
Vehicle_make_model_key
Vehicle_make_cd
Vehicle_model_cd

To me, that seems to complicated. Models are always going to betied to a Make, right? Even if there was a Ford Thunderbird and aHyundai Thunderbird, you'd want to treat them as their own separateentities. Both Thunderbirds might not necessarily be cars, soyour design falls apart. And the vehicle type is tied to thespecific model, not to the make, right? Maybe I am not looking atit closely enough, but I think you could probably simplify it like this:

vehicle_type
vehicle_type_cd
vehicle_type_desc

vehicle_make
vehicle_make_cd
vehicle_make_desc

Vehicle_make_model
Vehicle_make_model_key
Vehicle_make_cd
Vehicle_type_cd
Vehicle_model_desc

|||Perfect. I am convinced. Make writing the procs a lot easier as well. :)
|||

Another question already.

Okay lets say that I am actually selling vehicles.

So if someone is listing a truck I want to capture different things than if they are selling a car.
For example I want to capture towing capacity if they are selling a truck but not a car. On the other hand I want to capture mileage whether its a car or a truck.
How would you do the table layout for something like that?

|||Well, one way you could do it would be like this:

attributes
attribute_cd
attribute_desc

vehicle_type_attrbitutes
vehicle_type_cd
attribute_cd

The attributes table would have entries like "towing capacity" and"mileage". The vehicle_type_attributes table would have onerecord for every attribute applicable to the specified vehicle_type.
|||

But then how would I store that data in the item table:

So lets say I have a table
item_details
item_id
vehicle_type
Vehicle_make
Vehicle_model
Vehicle_year
Vehicle_mileage
Vehicle_description
Now I don't want to add the value vehicle_length becuase that is only applicable if the type of vehicle is a boat. So Where do I store that?
item_boat_details?
And then only check that if its a boat? Then I would need conditional SQL which seems silly?
Thanks

|||Well, then you could have:

attributes
attribute_id
attribute_desc

vehicle_type_attrbitutes
vehicle_type_attributes_id
vehicle_type_id
attribute_id

item_details
item_id
vehicle_type_id
vehicle_make_id
vehicle_model_id

item_attributes
item_attributes_id
item_id
attribute_id
item_attribute_value

Year, mileage, length, and description could be attributes, and theirvalues for the specific vehicle would be found in the item_attributestable.
And yeah, I know, I've been all over the place with the keys for thesetables. My personal preference (and you will find many validarguments against it), is a single surrogate key for each record, and Iuse "id" for these. Those table structures above more closelymatch how I'd structure it (well, except for the underscores, which Idislike).
|||

Okay. So I am kind of scared of dynamic attributes.

What about having a table for suv_details, and a table for car_details, and a table for truck_detaiis. Etc.

|||Sorry, I don't think that is a good idea.

Database Design Question

Anyone know a good database scheme for storing formatted text? The text will be coming from XML in this type of format:

<text id="01">
<headline>Here is the title</headline>
<subhead>Here is a subhead</subhead>
<paragraph>Here is some text</paragraph>
</text>
<text id="02">
<headline>Here is the title</headline>
<subhead>Here is a subhead</subhead>
<paragraph>Here is some text</paragraph>
<subhead>Here is a subhead</subhead>
<paragraph>Here is some text</paragraph>
</text>
... etc

How can I store in a database and keep the headlines, subheads, and paragraphs delimited without just storing the XML tags and keeping everything in order (each "text" may contain multiple paragraphs and subheadings ... )?

I'm just wondering if anyone has done this before and what has worked for them ... just looking for opinions on the most efficient way to accomplish ... thanks!If the data length will be less than 8000 characters drop the data into an a varchar column. If the data will exceed 8000 characters you will have to drop the data into a text column.|||Originally posted by smorton
If the data length will be less than 8000 characters drop the data into an a varchar column. If the data will exceed 8000 characters you will have to drop the data into a text column.

Thanks for the reply. I'm looking for the most efficient way to set up relationships that will keep my text formatted the same way it is in the XML doc and in the same order. Any ideas?|||Again, why don't you just drop this into a varchar or text field? Do you need to be able to query on different Tags? I don't get why you need to establish relationships to the XML.

blindman|||I need to be able to store other properties about each piece of text ... I guess this could be done with attributes of the XML tags. Is that the best way? Just dump the XML right into the db? Thanks!|||What other attributes?

How do you define a piece of text? One line? All the text between two tags?

What about nested tags?

blindman|||There will be no nested tags ... no need for them at this point. Attributes will be font size, family, color, etc ... and a transition id.

(I'm using ASP to generate XML for Flash ... but I also need to save the everything I write the XML in a database).

So I guess I could just store in the db as follows:

<text id="1">
<head font="arial" size="12" color="000000" transition_id="1">This is the title</head>
<sub font="arial" size="11" color="000000" transition_id="2">This is a sub title</sub>
<para font="arial" size="10" color="000000" transition_id="3">this is a paragraph</para>
<para font="arial" size="10" color="000000" transition_id="3">this is a paragraph</para>
<sub font="arial" size="11" color="FF0000" transition_id="2">This is a sub title</sub>
<para font="arial" size="10" color="FF0000" transition_id="3">this is a paragraph</para>
</text>
... etc

Does that sound right to you? Thanks!|||(follow up) ... text is everything between 2 tags.|||Well, you could have each line be an independent record containing both the text and the common attributes as separate fields. To keep the lines in order, use an incrementing key.

blindman|||you can also identify all the attributes that define a piece of text and separate them into a set of tables that would contain key-based references, all grouped together by a page reference (if applicable).|||Thanks guys. This is kind of what I was thinking ... just wanted to get an expert's opinion. If I use both the incrementing key and the page id with several related tables then I can keep the data all seperated out into organized tables and carry all the required attributes. Then when I am ready to write to XML, for each page I just do a select across the related tables by page id and order it by the incremented order id.

That brings the text out for each page in the right order with all of the formatting in place.

Sounds right to me. Thanks again!

Database Design Question

Question 1:

I have a contacts table which holds different types of contacts. I contact can only have 1 type. I have a contact types table and store the id of the contact type in the contacts table. I also have another table (table 1 for example) that has a 1 to many relationship with a contact. A contact can have 1 or many of table 1. But only a certain type of contact. Not all contact types have this relationship with table 1. They aren't related to table 1 at all. Is this ok, and how is it represented in a database diagram?

Question 2:

I have a contacts table and an address table and a contact can have 1 or many addresses. I also have a vendors table. They will also have addresses. I want to share the address table with contacts and vendors. Would I just have an address id as a primary key. Use the contact id or vendor id (depending on what type the address is for) as a foreign key, and then also have another column in the address table that held lets say a 0 or 1 depending on whether or not it was a contact address or a vendor address? Would this be the proper way to do this?

Any suggestions are welcome and thank you in advance!Question 1:
Is this ok, and how is it represented in a database diagram?most definitely yes, it is okay

diagram? diagram?

(you have to imagine jim mora saying "playoffs? (http://youtube.com/watch?v=5rmtO9_wzlI)")

is this a homework assignment?

Question 2:
Would I just have an address id as a primary key.yes

the other part is trickier

you could, for example, have both a contact id and a vendor id foreign key in the address, both of which must be nullable, since one of them (the one it isn't an address for ;)) will be null on every row

i personally would not have a 0 or 1 flag for whether or not it was a contact address or a vendor address|||lol...no..not a homework assignment. Just trying to be very organized on this project! Thanks for your reply. Would you recommend sharing the address table, or, would you have a separate address table for contact and vendor? I would think it makes sense to have only 1 address table.|||I would think it makes sense to have only 1 address table.okay, let's go with that for a second

why?|||why what...have a diagram, or have 1 address table?|||why does it "make sense" to have only one address table? how is it better than two?|||less duplication? Can I run another design by you? I'm really not doing homework...it's been a long time since I've done any database design so I'm rusty.

I have this situation.

A person can hold a season ticket for 1 or more teams. A team has more than 1 game and a game has more than 1 ticket. Here is how I'm designing my DB (not, only including necessary fields right now) Does it make sense to you?

Table:
tblTeam
Field(s):
TeamID PK

Table:
tblContacts
Field(s):
ContactID PK
(note: I will be using the contacts table for people other than season ticket holders as well)

Table:
tblSeasonTicket
Fields(s):
SeasonTicketID PK
TeamID FK
ContactID FK

Table:
tblGames
Field(s):
GameID PK

Table:
tblTickets
Field(s):
TicketID PK
SeasonTicketID FK
GameID FK

Appreciate your thoughts if you're willing to give them.|||less duplication? i don't think so

how often will a vendor and a contact share an address?|||sorry...what I meant was less tables...I'm going to have the same fields for an address. instead of creating a new table, I would only have to add a field. I actually prefer your way with the two tables...I find that easier to keep track of.|||you can bill me for it if you want|||no, my answers on public discussion forums are always free :)|||great...do you have a comment about my ticket scenario? :-)|||is it possible to get a ticket for a game that isn't a season ticket?|||no, it is not possible to get a ticket for a game that isn't a season ticket

Database design problem

We have to design a database for UserInfo. We already have a user
table, and contains userid. There are three user type(ex. student,
teacher,admin), they have their own tables. i don't know how to add the
personal infomation field. One way is putting some common field into
the user table, the other is laying the infomation of subtype into
their own tables.Which is better?Is personal info the same for all types? is it present in all types? If yes,
feel free to add it to the user table.
MC
"Readon Shaw" <xydarcher@.163.com> wrote in message
news:1132886720.966817.144210@.o13g2000cwo.googlegroups.com...
> We have to design a database for UserInfo. We already have a user
> table, and contains userid. There are three user type(ex. student,
> teacher,admin), they have their own tables. i don't know how to add the
> personal infomation field. One way is putting some common field into
> the user table, the other is laying the infomation of subtype into
> their own tables.Which is better?
>|||yes, but what is the advantages of that?
MC wrote:
> Is personal info the same for all types? is it present in all types? If ye
s,
> feel free to add it to the user table.
> MC
> "Readon Shaw" <xydarcher@.163.com> wrote in message
> news:1132886720.966817.144210@.o13g2000cwo.googlegroups.com...|||Readon Shaw wrote:
> yes, but what is the advantages of that?
The same advantage as that of having a user table at all - to help you
to apply constraints against it and therefore ensure the integrity of
your data. For example, if you have a name or email address that's
common to all types of user you'll perhaps want to add a constraint
that the address or name be unique across all users. That's much easier
to do if they are all in one table.
In general, the fewer places in which one attribute can appear, the
fewer the opportunities there are for anomalies and incorrect data.
David Portas
SQL Server MVP
--|||Phew, thanks David, I was trying to come up with a short answer :).
In addition let me point out that the entity in this design is User, and
that 'additional' tables might be called 'derivations'. Since the
information you're adding to design is based on entity and not on
derivation, you should apply it there. And only there.
Am I making sense here?
MC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132916473.316709.85460@.f14g2000cwb.googlegroups.com...
> Readon Shaw wrote:
> The same advantage as that of having a user table at all - to help you
> to apply constraints against it and therefore ensure the integrity of
> your data. For example, if you have a name or email address that's
> common to all types of user you'll perhaps want to add a constraint
> that the address or name be unique across all users. That's much easier
> to do if they are all in one table.
> In general, the fewer places in which one attribute can appear, the
> fewer the opportunities there are for anomalies and incorrect data.
> --
> David Portas
> SQL Server MVP
> --
>|||MC wrote:
> Phew, thanks David, I was trying to come up with a short answer :).
> In addition let me point out that the entity in this design is User, and
> that 'additional' tables might be called 'derivations'. Since the
> information you're adding to design is based on entity and not on
> derivation, you should apply it there. And only there.
> Am I making sense here?
>
> MC
>
Intuitively that makes sense, yes. More formally, this problem is the
subject of a design rule that Chris Date calls the Principle of
Orthogonal Design, which is used to prevent certain types of redundancy
and anomaly. Roughly this says that integrity constraints should
prevent data being recorded in more than one table if there is some
non-loss decomposition of the same data into fewer tables. That doesn't
absolutely preclude the user name (for example) appearing in both the
Teacher and Student tables but it does require that there should be
some constraint to prevent the same person appearing in both. For
practical reasons it is therefore more efficient to implement one
column and one constraint in one table rather than some more complex
constraints across several tables.
See the following article for more discussion of this.
http://www.dbdebunk.com/page/page/622331.htm
David Portas
SQL Server MVP
--|||Just to be certain, i didnt really try to explain the logic to you David,
but to the original poster :).
MC
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132923609.030176.20790@.z14g2000cwz.googlegroups.com...
> MC wrote:
> Intuitively that makes sense, yes. More formally, this problem is the
> subject of a design rule that Chris Date calls the Principle of
> Orthogonal Design, which is used to prevent certain types of redundancy
> and anomaly. Roughly this says that integrity constraints should
> prevent data being recorded in more than one table if there is some
> non-loss decomposition of the same data into fewer tables. That doesn't
> absolutely preclude the user name (for example) appearing in both the
> Teacher and Student tables but it does require that there should be
> some constraint to prevent the same person appearing in both. For
> practical reasons it is therefore more efficient to implement one
> column and one constraint in one table rather than some more complex
> constraints across several tables.
> See the following article for more discussion of this.
> http://www.dbdebunk.com/page/page/622331.htm
> --
> David Portas
> SQL Server MVP
> --
>