New Fleet/Roster database - Design Proposal

[Guest Antilles]

Before I start, I'll say that the SC gave me the go ahead to start this thread.

We're drawing up a design proposal for the new fleet/roster database now, and it would be nice to have ideas on how it should work, what features it should have.

Here are my preliminary ideas:

Description: A database system which manages allegiances (Galcom, Earthcom,

Insurgent, undesignated), Fleets (Prime Fleet, ISS Fleet, Insurgent One,

etc), and Commanders/ranks.

I think this would be a hiearchical system, so when a user views the top

level of the database they see the allegiances, then if they click on say,

Insurgent they will see all the insurgent fleets, then if they click on

fleet Insurgent One they will see all the commanders in that fleet.

Admins would be able to add and edit fleets and allegiances, while fleet commanders can edit their fleet and their memberships, and commanders can edit only their own record.

Please everyone give their ideas

------------------

Commander Antilles

GCV Hood

[Rattler]

One point of order here Commander. There's only one Fleet in the Insurgency, that would be Spectre and there's only one Insurgent One and that would be me. Ron in my absence.

------------------

Rattler, Insurgent One

Commander-In-Chief,

Insurgency

Official BC3K Tester

[email protected]

ICQ 12894104

"Life consists not in holding good cards but in playing those you hold well! - Josh Billings

[Steve Schacher]

Well, I took a stab at a first-cut data model. It's over here: http://members.home.net/srschacher/BC3K/BC..._Data_Model.htm

It's a 3rd normal form data model developed in PowerDesigner, so I can generate almost any kind of DDL from it.

This model will allow you to keep information on commanders (rank history, ship history, fleet assignment history) and fleet history (organizational structure, allegiance).

On the fleet side, you can define the various fleets, the positions within the fleets, and which positions report to which other positions.

On the commander side, you can define the commander, assign a rank, assign a ship to the commander, and assign the commander to a fleet position.

The relationship is between commanders and fleets -- not ships to fleets. Ships are expendable, commanders are not. If you lose your ship, you don't lose your fleet affiliation, therefore the link to fleets is not through the ship.

The model can be expanded to do all sorts of things, such as make individual operational assignments to commanders, etc. This is just a first cut. Tell me what you want it to do and I'll model it. When someone is ready to program it, I can create the necessary DDL.

[This message has been edited by Steve Schacher (edited 05-08-2000).]

[Guest Ron Wallin]

Antilles, do we know you under a different name?

Anyway, Admins should have access to the entire DB, FC's access to only their fleet, and all others access to only their record.

items in the database:

Player's real name (optional)

Cmdr. Name (Indexed, with only one occurance allowed)

Ship name (indexed with only one occurance alowed)

Rank

Affiliation (GALCOM, EARTHCOM(or is EC only a fleet?), Insurgent, Other races(if DS allows players to choose other races)).

Fleet (optional, since not all castes will necessarily belong to a fleet)

Wing (or subgroup if it exists)

Status (active/inactive/mia, etc.,)

Security Category (defines admin, FC, or normal player. this would be a feild only visible to admins)

Username (invisible to all but owner and admin)

Password (invisible to all but owner and admin)

other possibles:

a: number of kills in online matches but only if there was a way to grab the info from the server independant of the user

b: online mp ranking (based on number of kills; kills per minute; shots hit vs. shots missed; etc.

items 4, 5, 6, 8, 9 would be hard coded picklists (only DS or approved admins could change)-- this would also require that final ranking structures be completed by non GALCOM fleets (aka, Insurgents)

item 7 would be defined by admin or Fleet Commander

item 1, 2, 3, would be indexed and allow for only one occurance within the DB. Item 1 should be optional to allow for those that don't like their rl names to be known (or simply don't like their rl)

This is similar to a db Jon Ballinger and I created for the Insurgents. Unfortunately, he became busy with RL and wasn't able to finish the PHP3 encoding. I've still got the access db if someone wants to see it.

*** sorry steve, you posted while i was writing... can you make an image that's a touch larger and easier to read... i wasnt' able to make out the text between the bubbles.

[This message has been edited by Ron Wallin (edited 05-08-2000).]

[Guest Cmdr Josh Dilvorn]

Between Fleet Commander and Commander I think there should be another layer to accomodate the seperate wings within the fleets.

It could end up something like: Affiliation (GALCOM, Insurgent etc) ==> Fleets (ISS, Prime etc) ==> Wing ==> Commanders

For an example of what I was thinking, check out this quick mock up

------------------

Commander Josh Dilvorn

GCV Conquest ISS06

Defense Wing Commander - ISS Fleet

ISS Homepage GCV Conquest Home

[This message has been edited by Cmdr Josh Dilvorn (edited 05-08-2000).]

[Rattler]

(RP)

Ahoy GCV Hood!

Greetings and Salutations from Spectre Fleet.

Had I payed attention to the Fleet Designator on your Vessel, I would not have confused you with one of my own. My appologies to you and your crew.

(/RP)

I, or any of these fine Commanders stand ready to provide any assistance that you need in the preparation of this data base. Say the word and we'll be there for you.

------------------

Rattler, Insurgent One

Commander-In-Chief,

Insurgency

Official BC3K Tester

[email protected]

ICQ 12894104

"Life consists not in holding good cards but in playing those you hold well! - Josh Billings

[Supreme Cmdr]

quote:

---

a: number of kills in online matches but only if there was a way to grab the info from the server independant of the user

b: online mp ranking (based on number of kills; kills per minute; shots hit vs. shots missed; etc.

---

Ron

I will have Nick (the mp engine architect) put in support for stats checking. I can then pipe out number of kills and mp ranking only. Nothing else. However, for that to work, they players on a mp server will have to ensure that their player ID that they log on with, matches the roster entry or it won't update it. And of course if somone gets killed, the records would be reset. I have forgotten how other games, eg PXO over at Freespace, handle this, but I'm sure that it is not persistent.

[This message has been edited by Supreme Cmdr (edited 05-08-2000).]

[SpacePhish]

S.C,

You can save the record on the client machine after each game, like they do in Delta Force 2. Each player has an record of his soldier, like death vs kill...

$0.02

[Parias]

Nope, if he does that it's too open to hacking.. then we'd have newbies floating about with the kill count of the average Veteran.. of course, it'd be easy to tell who had hacked since the rest of them would probably just set everything at 999 and laugh psychotically.. untill we punted 'em from the game anyways

But, this is off-topic of the thread.. I'm sure the SC has a handle on this

------------------

Cmdr. Parias

Spectre Fleet

Chief Engineer, Starstation Destiny

ICV-Galatae

ICQ:1293359

Staff member at HardNews

"I took this job because I just really love to blow sh*t up!"

[Supreme Cmdr]

I'm not interested in death vs kill ratios. And I'm certainly not going to use player side records which can be hacked.

[Gallion]

My six bits

I agree with what Ron posted in reference to the backside of the database. Once that and the relationships are hammered out then the frontend will/should be a breeze.

As to keeping MP player stats, this will have to be server feed, e.g. Galcom Online. Which is great one year plus from now. How to get reputable player stats for BC:M? Next to impossible unless the MP tallys from BC:M hosts are transmitted to an Official Galcom.net location. This would only work if the BC:M host is trusted or registered. More smack than it is worth for MC:M IMO. I agree with the SC, where there is a will they will be a way to embellish ones MP statistics.

TTFN

[This message has been edited by Gallion (edited 05-08-2000).]

[SpacePhish]

Oh yeah, I'm sure some-one would hack the record to death...so my bad.

This also brings up the topic of trainers etc etc... to my understanding that if you store game-variables on the server side cheating(trainer etc..) would be very hard but still possible... off topic, my bad.

[Gallion]

This would make things more difficult IMO & IME. However with the caliber of the majority of Commanders on this forum the cheaters always prosper axiom may be a moot point.

With that said (went on the limb there), I have full faith and confidence that the MP stats on the Galcom Online servers will be somewhat tamper proof from the outside. The SC is somewhat familiar with data manipulation techniques from unauthorized personnel I do believe.

[Guest Ron Wallin]

Not a problem, Steve. Should be in your box right now. It's populated with old members, etc. but because email addy's are included, I'm requesting that you not share it with anyone without my permission. Thanx.

You should find it a simple DB with a few relational tables for pick lists. Nothing fance, just clean and fast. The form view and query were not completed simply due to it being a launching point for a PHP3 encoded web page. Alas, the plans of mice and men...

As for my list of items, i hadn't had the chance to view your post as i was writing mine while you posted...

====

Dilvorn, your requests for structure was covered in my data points and i believe in Steve's as well... As for your mockup, I don't like having to click through a number of pages like that. Reminds me too much of MS's site with thier logons etc.

I prefer a form view with all the options up front in one page for entering information(much like editing your profile here on the forum).

=====

DS: Are you saying that the stats will be based on persistent play. If I die at anytime, I drop to the bottom of the list? Wow, talk about pressure to perform. Is it likely that any player will last long enough for persitent stats to be accurate for ranking?

Highest number of kills i can understand. It's a simple way of determining the king of the hill. However, if your ranking drops everytime a newbie gets a lucky shot, the king of the hill will change on a minute by minute basis as the person on top will end up on the bottom every time he/she plays.

Anyway, this is a discussion of another thread possibly...?

As for login ID's, unless the login is tied to the database's username and password, anyone can login using any name at any time.

The psuedo player will either get a load of kills and help the owner of the ID or they will die and quickly put the owner of the ID at the bottom of the ranking (if the ranking is as you described it).

Just some points to think about.

Games like half life, quake, etc. don't even bother with persistent ranking. Go to The CLQ and look me up: DrStrangeLove. You'll see my stats, and they are mine. I made sure to check the list for a handle that was unique enough.

Anyway, TheCLQ simply polls ALL known game servers for the stats. Providing a player has played for at least an hour, his/her stats are logged.

=====

Phish: I made it clear in my post that the tracking would have to be server side. I didn't think explanation was necessary. Doh!

====

Gallion, the ranking system could simply use a list of known "trusted" servers. Those that play on the trusted list would get ranked. Those just having fun on whatever server is running, wouldn't. Very simple.

However, providing that BCM hits big, the number of servers will blossom. How will one easily determine what servers are authorized out of 2000+ servers? If the stats reporting is part of the server module, then "trusted" servers would not be necessary! The ranking system would simply poll the servers for the stats and update the DB.

====

OK, enough ranting. It'll be nice to see this come to fruition.

*** doh, you did it again, Steve. Next time i want to post here, i'm going to wait 10 min's as I'm sure that you're probably posting... time to pull out the telepathy helmet!

[This message has been edited by Ron Wallin (edited 05-09-2000).]

[Guest Antilles]

Eclipse, I hadn't realised that the name Antilles was taken, I've now changed to another name (see sig). But my posts still show up as Antilles so I think I have to sign up another login.

Steve and Ron, you've more or less designed this database already The only additional thing I've been able to think of is: Certain ship types are only available to certain allegiances I believe, for instance an insurgent can't command a galcom cruiser. We need some way to make it that commanders can only be assigned a ship that is available to their allegiance.. perhaps we could have a table of available ships for each allegiance, or just one table but with a field to indicate which allegiances can have this ship.

---

Commander Hubbcold

GCV Hood

[This message has been edited by Antilles (edited 05-09-2000).]

[Supreme Cmdr]

quote:

---

DS: Are you saying that the stats will be based on persistent play. If I die at anytime, I drop to the bottom of the list? Wow, talk about pressure to perform. Is it likely that any player will last long enough for persitent stats to be accurate for ranking?

Highest number of kills i can understand. It's a simple way of determining the king of the hill. However, if your ranking drops everytime a newbie gets a lucky shot, the king of the hill will change on a minute by minute basis as the person on top will end up on the bottom every time he/she plays.

Anyway, this is a discussion of another thread possibly...?

---

This is for a whole other thread. Frankly, I don't have a clue how I'm going to do it yet.

[Guest Ron Wallin]

quote:

---

Originally posted by Antilles:

The only additional thing I've been able to think of is: Certain ship types are only available to certain allegiances I believe, for instance an insurgent can't command a galcom cruiser.

---

Hubcold, I'm not sure that your assumption is correct. As the game stands now, Insurgents (even Gammulans) command BC's. I'm not sure what DS's plans are for the future, but considering that most Insurgent Commanders are defectors from GALCOM, I see no reason why they wouldn't bring their BC along with them.

[This message has been edited by Ron Wallin (edited 05-13-2000).]

[Supreme Cmdr]

Any race/caste can command any ship/vehicle. I'm not going to start placing restrictions on that.

The only thing I'm thinking about is that in multiplayer, you can't just have everyone log on and grab a Stormcarrier. So, I'm going to make it based on EPs and rank, so, no matter what asset your station has, if you're not the proper rank and have the the required EPs, they won't let you take certain ships. So, you could grab a MK3 battlecruiser and go rampaging until you get promoted or something, and then go back to your base and grab a Stormcarrier if there is one available. There will be a fixed number of assets at each station/base.

Of course, if you can't find one at your base or you are not the proper rank etc, you can always go jack one from some hapless player or NPC, but thats for GCO, not BCM.

[Steve Schacher]

DBMS = Data Base Management System

I remember Derek saying he's using MySQL. I don't have it, and my version of PowerDesigner doesn't generate it, so in the end, I will have to generate the most compatible DDL for MySQL and then it would be modified by hand.

Before we get that far, I wondered if anyone who wants to code a front-end would like a prototype database to use in the interim.

[Supreme Cmdr]

Dunno what Steve's doing with a DBMS system, but this was supposed to be Antilles' gig. So, can someone tell me wtf is going on? Or is this one of these tasks where I have to take control and hire someone competent enough to actually pull it off?

No offense, but BCM is due out soon, and so far, you ladies are just screwing around with this.

And yes, the site is on a Unix box running mySQL and PHP3.

[Rattler]

I personally thought that you had already assigned Antilles of the GCV HOOD to do the honors SC. I don't know where the discussion of "re-inventing the wheel" came from.

------------------

Rattler, Insurgent One

Commander-In-Chief,

Insurgency

Official BC3K Tester

[email protected]

ICQ 12894104

"Life consists not in holding good cards but in playing those you hold well! - Josh Billings

[Steve Schacher]

I'll add my recollections...

From thread: http://www.3000ad.com/ubb/Forum7/HTML/000623.html

quote:

---

quote:

--------------------------------------------------------------------------------

Now that we have mySQL, am gonna look for volunteers to setup the new fleet and roster databases. Anyone up to the challenge?

--------------------------------------------------------------------------------

My coding skills are caput, but I'm a data modeler and JAD facilitator. I can certainly help in the relational data modeling for the new database. What tools would be necessary for the new roster database? What is mySQL (I'm in an Oracle shop)? I'd volunteer to work with developers to design a good, normalized database.

---

You asked for volunteers and I offered my services as a data modeler. When Antilles started this thread, I bellied up and started a draft data model for people to comment on. As the comments rolled in, I adapted the model.

There is no "DBMS System." There is only a conceptual design based on comments that people posted to this thread. I have data modeling tools and thought it would help people to visualize what they are asking for.

Since the thread was silent for a few days, I offered to generate a prototype database in case someone else was interested in prototyping a front-end. If this isn't going where you want it to, then I will graciously step back.

Ron: I tried accessing your page, but all I got was a white screen. Am I missing something?

[This message has been edited by Steve Schacher (edited 05-13-2000).]

[Supreme Cmdr]

Steve

quote:

---

Sorry Derek, this seems to merely be a discussion between myself and Steve

---

Then you guys take it to email or create another thread. This thread is only for the discussion as it pertains to this site's roster and Antilles task at hand. I have subsequently deleted all non-related posts from this thread. The only posts that I want to see, including design specs, suggestions etc, should only pertain to this topic.

quote:

---

Since the thread was silent for a few days, I offered to generate a prototype database in case someone else was interested in prototyping a front-end. If this isn't going where you want it to, then I will graciously step back.

---

If I am confused as to the intent (which was not apparent in any of your posts until this one), what makes you think anyone else understand what you are trying to achieve? I have seen no indication that you are working with Antilles on this and I see no evidence of collaboration of same. With that, I can only conclude that this is going to turn into another farce that carries on for weeks, everyone tugging in different directions; and in the end, I have to step in and sort it out.

So, if you are collaborating with Antilles, I need to hear this from him and not you. I would also like to know what this collaboration entails. Your system, DBMS or not, is useless in the general scheme of things because it is unuseable in the scope of the task at hand and will only lead to the confusion that is now ensuing.

Until Antilles informs me that he is in the least interested in whatever it is you guys are doing, I do not want to see it in this thread.

Thanks for the effort but I simply do not have the time to babysit this mess. And if that Roster is not up and running by June, in some form or another, I will have someone else do it. I won't forget the downtime that would be caused and anyone associated with it, can kiss any chances of getting anywhere near the upcoming BCM Beta test, goodbye. The first time those in question ever see it, will be when they buy it from a store.

That is all.

[This message has been edited by Supreme Cmdr (edited 05-13-2000).]

[Guest Antilles]

I apologize for not making this clearer.. I contacted the SC by email about the db and I've been given the task of creating a design proposal for the Fleet Database which I'm to submit to the SC. When It's approved I'll make the MySQL database and all php scripts/frontend necessary.. although I'm not too good with graphics so I'll need help with that. A lot of good ideas were posted in this thread and it helped a lot coming up with the design for the database.

I'll now write a the design proposal and submit it to the SC as soon as it's ready, thanks everyone for the design ideas.

------------------

Commander Hubbcold

GCV Hood

[Supreme Cmdr]

phew!, that about clears that up I think. Thanks!

As for graphics, don't worry about that. Use placeholders for now and once I OK the design proposal, I'll commission them. Tac has some landlubbers in his crew who are good at web graphics.