Relational Database design problem

Hi,

I have a very important question about the database design. Suppose that I have a table named as tblPerson with the following Fields.

tblPerson:
--------
PersonID
PersonName

And I have a another table that has the Person Credit card Information:

tblPersonCreditCard:
---------

CreditCardID
PersonID
CreditCardTypeID

And Another table which contains the type of creditcards:

tblCreditCardsType:
----------

CreditCardTypeID
CardName

NOTE: Suppose CreditCardTypeID 1 = "MasterCard", 2 = "Visa" , 3 = "platinum"

Now if a person suppose "john" has three credit cards then it will be stored as.

tblPersonCreditCard:
---------

CreditCardID PersonID CreditCardTypeID
1 1 1
2 1 2
3 1 3

Each if the number belong to one Field.

Which means that "john" has three credit cards

Now as u see that in tblPersonCreditCard john's id, which is 1 is repeating 3 time. how can I design a database in which the id does not repeat. I have heard that this can be done by using the bitwise operator but HOW.

thanks in advance,

AzamI don't see a problem with each record in tblPersonCreditCard including a foreign key to the credit card's owner. Is there some problem you're having with doing so?|||No i am not having any problem. But I just wanted to know that if there a better way of doing the same thing.|||There is no reason to do what you are attempting to do. To create a many-to-one relationship, you need to have a foreign key which will potentially repeat in the child table.|||I have heard that you can use some sort of the bit wise operator to store all the relationship of the person in a single field. I have no idea how ??|||I think you mean "bitmask", actually. Here'san article, if you're really interested.

Reinstall SQL Sever 2000

Hi all,

I have a server that is missing some important files, and instead of doing process of elimination, I feel it is best just to reinstall 2000.

Now is there a way to reinstall 2000 and just getting the files that the server is missing?

Is there any guidelines or steps for reinstalling 2000 out there?

Thanking you in advance.

LystraDoes anyone have any answers?

Thanks

Lystra|||I have look everywhere in order to get detail for reinstalling 2000, and have come up with anything.

Will I need to uninstall SQL and then install it all over again to get the missing files?

Thanks

Lystra|||You can try to re-install the service pack. That will replace some files, but not necessarily all of them. And if you are missing enough files that SQL Server will not start up in the first place, you may end up having to just uninstall and re-install SQL Server, altogether.

And, of course, you may want to look into restricting the permissions so folks can not accidentally delete these files.|||Thanks for responding.

As for the data files once I uninstall will the files be remove?

Do I need to move the data files of the server??

Lystra|||I have not uninstalled SQL Server many times, so I do not know if the data files are left on the disk. It is best to save off backups of all of your databases (master and msdb included), before you start this sort of thing. Once you are certain you can get all of your data back from any mishap, then you can start trying to get SQL Server off the box.|||True, its always better to keep the upto date backup of the databases and store them in other location until the resinstallation process is completed.

There aren't any defined steps to reinstall just install the CD and continue as specified in the screen. Its better to do it from scracth by uninstalling current version , remot registery referenced keys, reboot the box and then reinstall SQL.|||Hi

I have found a MS Knowledge base article on How to manually remove SQL server 2000 Default, named, or virtual instance.

I help you with the reinstall process.

Article # 290991

Thanks for all your help

Lystra

ReIndexing while using replication Important

Does anyone know of a good set of guidelines to follow, after running DBCC
Showcontig on a publisher of replication and DBCC reindexing is called for?
What are the pitfalls and things to watch out for? I can't afford to make a
mistake since there are three servers involved and it's in production.
I can do the reindexing during night time hours. I've read a lot of material
but haven't found "replication considerations" and have not found anything
other than generalities when explaining how do analyze the Index fragmenting
statistics.
Matthew Mark
For merge replication it helps to reindex or defragment the msmerge tables.
You need to stop the merge agents which you are doing this. Ideally you
would stop all users while you are doing the reindexing.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
> Does anyone know of a good set of guidelines to follow, after running DBCC
> Showcontig on a publisher of replication and DBCC reindexing is called
> for?
> What are the pitfalls and things to watch out for? I can't afford to make
> a
> mistake since there are three servers involved and it's in production.
> I can do the reindexing during night time hours. I've read a lot of
> material
> but haven't found "replication considerations" and have not found anything
> other than generalities when explaining how do analyze the Index
> fragmenting
> statistics.
> --
> Matthew Mark
>
|||Here's what I understand from this.
Since I couldn't find a MSMerge table I assume you mean to reindex the
Article (tables) in MSarticles from the publisher.
If so, how does this fall into the procedure. For example, based on what I
know now, here's how I would do this.
1. Turn all merge agents off.
2. Put DB in restricted mode.
3. Run DBCC reindex, looping through all tables in this database.
4. Put dB back in Full access mode.
5. Turn merge agents back on.
So, how does your suggesting fit in or modify this plan?
Does the reindexing naturally replicate to the subscribers? or ?
thanks
Matthew Mark
"Hilary Cotter" wrote:

> For merge replication it helps to reindex or defragment the msmerge tables.
> You need to stop the merge agents which you are doing this. Ideally you
> would stop all users while you are doing the reindexing.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
>
>
|||reindexing does not propagate to the subscribers, I meant that you should
reinindex msmerge_contents, and msmerge_tombstone.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...[vbcol=seagreen]
> Here's what I understand from this.
> Since I couldn't find a MSMerge table I assume you mean to reindex the
> Article (tables) in MSarticles from the publisher.
> If so, how does this fall into the procedure. For example, based on what I
> know now, here's how I would do this.
> 1. Turn all merge agents off.
> 2. Put DB in restricted mode.
> 3. Run DBCC reindex, looping through all tables in this database.
> 4. Put dB back in Full access mode.
> 5. Turn merge agents back on.
> So, how does your suggesting fit in or modify this plan?
> Does the reindexing naturally replicate to the subscribers? or ?
>
> thanks
>
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||When I do reindex, and loop through the tables, doesn't it include these
tabels too?
Matthew Mark
"Hilary Cotter" wrote:

> reindexing does not propagate to the subscribers, I meant that you should
> reinindex msmerge_contents, and msmerge_tombstone.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...
>
>
|||Matthew,
this partly depends on what you are reindexing - system tables or user
tables. Generally speaking though, I ensure synchronization isn't occurring
while reindexing takes place, otherwise there tend to be job failures
because of blocking.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
I have never run reindexing on a replicated db. I have ,though, run a script
that runs the reindexing on all user tables. Are these MSarticles etc tables
system tables? I'd assume they are since the replication process creates it.
To avoid asking you a lot of specific questions, is there a comprehensive
guide I can read? The microsoft info online is usually good, only if you
already know the pitfalls. I don't.
If I make a mistake here because I didn't do my homework..., well, I'm sure
you understand. We don't replicate on our development server, so I can't
exactly practice ahead of time.
Matthew Mark
"Paul Ibison" wrote:

> Matthew,
> this partly depends on what you are reindexing - system tables or user
> tables. Generally speaking though, I ensure synchronization isn't occurring
> while reindexing takes place, otherwise there tend to be job failures
> because of blocking.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I am unsure what you mean here. Can you clarify it? I normally issue the
following dbcc dbreindex('msmerge_contents')
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:9038947F-DAA7-476C-92ED-729E905E77E3@.microsoft.com...[vbcol=seagreen]
> When I do reindex, and loop through the tables, doesn't it include these
> tabels too?
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||Matthew,
I don't know of any guide about reindexing and replication. However I'm not
too sure there's much to it. If you know the difference between DBCC
DBREINDEX and DBCC INDEXDEFRAG, understand locking and blocking and can see
what the agents require access to in order to work, these are the basic
facts. All I can sey is that a simple guideline is to run DBCC DBREINDEX not
overly often and when you do, try to ensure that synchronization isn't
occurring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .